How to find which date is the latest

[Guest]

Hello to all
I have a table with 5 fields that users entering dates. My question is how
can i display in another field (in the form) the latest of all? All 6 fields
are in the same table.
Thank you

 

[Stefan Hoffmann]

hi Kostas,

I have a table with 5 fields that users entering dates. My question is how
can i display in another field (in the form) the latest of all? All 6 fields
are in the same table.

This sounds like a wrong table layout. Imho you should use something like

ID, DateType, DateValue

Here you could use a simple

SELECT Max(DateValue)
FROM table

otherwise you need a query using a function like:

Public Function MaximumDate(ParamArray AValues()) As Date

Dim Count As Long
Dim LowerBound As Long
Dim UpperBound As Long

Dim MaxDate As Date

LowerBound = LBound(AValues())
UpperBound = UBound(AValues())

MaxDate = AValues(LowerBound)
For Count = LowerBound + 1 To UpperBound
If Nz(AValues(Count), MaxDate) > MaxDate Then
MaxDate = AValues(Count)
End If
Next
MaximumDate = MaxDate

End Function

SELECT MaximumDate(field1, field2, field3)
FROM table

Just google for "access vba min max paramarry".


mfG
--> stefan <--

 

[Guest]

Thank you Stefan for your quick reply
The reason i have this design is because i want to keep track of the
appointments with our customers. That's why users enters the dates and the
floors that meetings will take place if they do take place (sorry for my
english). Your suggestion for a seperate table with id, date and floor is
beautiful. You tell me that with
SELECT Max(DateValue)
FROM table
i can populate another unbound field in a form that shows me the latest date
and floor of all dates?
I'm new to vba i forgot to tell you
TIA