Min across fields not rows

[buzzmcduffie]

I have a table that has been sent to me that has multiple time stamps done
when individuals update the fields. I need to find the min of 6 date fields.
can this be done in a query?

thanks!

 

[Jeff Boyce]

Access is a relational database, and expects to find multiple repeated
measurements vertically, not horizontally (as you would in a spreadsheet).
The Min and Max aggregation ("TOTALS") functions expect to find vertical
data.

You can export your data to Excel and use Excel's functions to find the
minimum of a row of values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

You can write a function to do that and call it from your query.
In your SQL:
SELECT MYMINFUNC(a,b,c,d,e,f) FROM....

In your module:
PUBLIC FUNCTION MYMINFUNC(a as date, b as date etc. etc.) As Date
' determine earliest timestamp
.... insert your code ...
MYMINFUNC = b (or whatever)
END FUNCTION

-Dorian

 

[John Spencer]

The easiest way would involve a VBA function. YOu can use the one below.
Paste it into a VBA Module and save the module with some name other than
fGetRowMin.

In your query,
Field: Earliest: fGetRowMin([Date field1],[Datefield2],[field3],[field4])


'Handles Text, date, and number fields
Public Function fGetRowMin(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum of a group of values passed to it.
'Sample call:
' fGetRowMin("-21","TEST","2", "3","4","5","6","7",0) returns 0
' fGetRowMin(-21,2,3,4,5,6,7,0) returns -21
'Handles text, date, & number fields.

Dim i As Long, vMin As Variant
vMin = Null

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
If Values(i) >= vMin Then
Else
vMin = Values(i)
End If
End If
Next

fGetRowMin = vMin

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County