UtterAccess.com
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?

I 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: 57,225
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: 57,225
From: SoCal, USA



Thanks. You may be able to determine the min and max values by creating a recordset based on that SQL.

To 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: 57,225
From: SoCal, USA



yw.gif

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:

DMin("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,072
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.

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


--------------------
If I have good Relationships I can have Access to anything.

“You CAN'T be a Leader , if you're NOT a Reader”.
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: 24th July 2014 - 12:44 PM