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