MAX Date

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,
On my form I have 4 text boxes called
"Servicing1","Servicing2","Servicing3","Servicing4". Here I input dates
used for showing the servicing/work dates on a vehicle. How can I display
the latest date from the 4 text boxes in a new textbox called "LastWork".

Regards

John
 
John,

This data should really be 4 separate records in a single field in a
related table, rather than 4 separate fields in one record of the base
table. Normalising your table design would probably simplfy a lot of
things.

Having said that, would I be right in assuming that the dates are
entered chronologically, i.e. they are entered as Servicing1 then
Servicing2 then Servicing3 then Servicing4? If so, you may be able to
use something like this...
=Nz(Nz(Nz([Servicing4],[Servicing3]),[Servicing2]),[Servicing1])
 
John, there is a function below that you can use to select the largest
non-null value from a number of inputs. By saving the function into a
standard module (Modules tab of Database window), you could put a text box
on your form and set its ControlSource to:
=Largest([Servicing1], [Servicing2], [Servicing3], [Servicing4])

However, this is only a klutzy workaround. The real problem is that you need
to create a related table, with fields:
ID AutoNumber
fkID foreign key to your main table's primary key
ServiceType value between 1 and 4?
ServiceDate date/time field.
Now you have only one field to search, and can do *much* more than with your
existing structure. This is a basic aspect of normalisation: whereever you
see repeating fields such as Service1, Service2, ... they always belong in a
related table.


Function Largest(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.

varMax = Null 'Initialize to null

For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next

Largest = varMax
End Function
 
Back
Top