UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | 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

New Member
Posts: 9



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: 58,549
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

New Member
Posts: 9



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

New Member
Posts: 9



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
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: 58,549
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

New Member
Posts: 9



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: 58,549
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
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
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 03:20 AM