UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

 
   Reply to this topicStart new topic
> Minimum Or Maximum Value In A Listbox Field    
 
   
umbata
post Mar 8 2010, 10:38 AM
Post#1



Posts: 9
Joined: 6-March 10



How can I determine the minimum or maximum value in a listbox field?
know how to count the rows in a listbox: Me.listbox1.ListCount
but how can I figure out the minimum and maximum values? Let's say I have a field called 'date', which is one of several fields in the listbox?
Thank-you!!
Go to the top of the page
 
theDBguy
post Mar 8 2010, 11:31 AM
Post#2


Access Wiki and Forums Moderator
Posts: 62,098
Joined: 19-June 07
From: SoCal, USA


Hi,
welcome2UA.gif
How are you populating the listbox?
Go to the top of the page
 
umbata
post Mar 8 2010, 11:35 AM
Post#3



Posts: 9
Joined: 6-March 10



doing some research online, i've put together the code below. seems like it may work, but how can I loop through values in the 7th field in the list box?

Dim date as Date
Dim mindate as Date
Dim maxdate as Date

minDate = 1/1/2015
maxDate = 1/1/1915
For i = 0 To me.listdataforplotting.listcount
date = Me.list_dataforplotting.value(i, 7) !is this the way to look through values in the ??
If date > maxDate Then
maxDate=Date
End If
If date < minDate Then
minDate=Date
End If
Next<
Go to the top of the page
 
umbata
post Mar 8 2010, 11:39 AM
Post#4



Posts: 9
Joined: 6-March 10



I am populating the listbox using a SQL statement as the rowsource.
Go to the top of the page
 
jzwp11
post Mar 8 2010, 11:54 AM
Post#5


UtterAccess VIP
Posts: 4,239
Joined: 6-May 05
From: Dayton, OH


Have you tried the Domain aggregate functions DMax(), DMin()?
DMax("datefield","thequeryyouuseforthelistboxrowsource")
Go to the top of the page
 
theDBguy
post Mar 8 2010, 11:57 AM
Post#6


Access Wiki and Forums Moderator
Posts: 62,098
Joined: 19-June 07
From: SoCal, USA


Thanks. You may be able to determine the min and max values by creating a recordset based on that SQL.
o create a recordset, you could try something like:
Dim varMin As Variant
Dim varMax As Variant
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Min([FieldName]) As MyMin FROM TableName")
varMin = rs!MyMin
Set rs = CurrentDb.OpenRecordset("SELECT Max([FieldName]) As MyMax FROM TableName")
varMax = rs!MyMax
Set rs = Nothing
(untested)
Hope that helps...
Go to the top of the page
 
umbata
post Mar 8 2010, 01:57 PM
Post#7



Posts: 9
Joined: 6-March 10



THANK-YOU!!!
This works:
Set db = CurrentDb()
Set qry = db.QueryDefs("E1_SelectedData")
Set rs = CurrentDb.OpenRecordset("SELECT Min([Sample_Date]) As MyMin FROM E1_SelectedData")
varMin = rs!MyMin
Set rs = CurrentDb.OpenRecordset("SELECT Max([Sample_Date]) As MyMax FROM E1_SelectedData")
varMax = rs!MyMax
Set rs = Nothing
Go to the top of the page
 
theDBguy
post Mar 8 2010, 02:44 PM
Post#8


Access Wiki and Forums Moderator
Posts: 62,098
Joined: 19-June 07
From: SoCal, USA


But I thought you said that your listbox RowSource was a SQL statement? If it was just a query, you didn't even need code. You can do what John mentioned. For example:
Min("Sample_Data", "E1_SelectedData")
and
DMax("Sample_Data", "E1_SelectedData")
Good luck with your project.
Go to the top of the page
 
dallr
post Mar 8 2010, 08:47 PM
Post#9


UtterAccess VIP
Posts: 3,075
Joined: 4-November 04
From: The Land of the Access Ninja's


I was trying to post this early on in the day but for some reason UA was not keeping me logged in so I could not have posted it. I see you have been given some alternative solutions that see good but I am just posting mine anyway. The only advantage to my solution is that if you embedded the sql directly into the listbox rowsource or changed the list box rowsource on the fly you can still get the new min and max value.
!--c1-->
CODE
Public Sub MinMaxList(fldName As String)
Dim sql As String, strMin As String, strMax As String, strFrom As String Dim rst As DAO.Recordset
sql = Me.List0.RowSource ' List0 is the name of your listbox
strFrom = Mid(sql, InStr(1, sql, "FROM"))
sql = "SELECT MIN(" & fldName & ")" & ", MAX(" & fldName & ") " & strFrom
Set rst = CurrentDb.OpenRecordset(sql)
strMin = "Min Value = " & rst.Fields(0)
strMax = "Max Value = " & rst.Fields(1)
MsgBox strMin & " " & strMax
rst.Close
Set rst = Nothing
End Sub

Dane
Go to the top of the page
 


RSSSearch   Top   Lo-Fi    22nd May 2015 - 08:11 PM