This is a design issue. You should be using two tables like this:
AddressTable
AddressID
AddressInfo1
...
Appointment
AppID
AddressID
Date
Info...
Then you would just do a MAX() on the query results.
Is there a reason you need a table structured the way you have it?
Anyway, on your data entry form, put down all of your fields, including the MAX field.
Then on the Before Insert AND on the Before Update event of the form, put this code:
Dim DateArray As Variant
Dim dtMax As Variant
Dim dtTest As Variant
Dim i As Integer
i = 0
DateArray = Array(Me.Date1, Me.Date2, Me.Date3, Me.Date4, Me.Date5)
dtTest = DateArray(i)
dtMax = DateArray(i)
i = i + 1
Do Until i = 5
dtTest = DateArray(i)
If dtTest > dtMax Then
dtMax = dtTest
End If
i = i + 1
Loop
Me.MaxDate = dtMax
I got this to work but it is a little flakey. See, all this code since the underlying database design is flawed. I am sure there is a more elegant way to do this, besides the redesign.
HTH
mmcdonal
Look it up at:
http://wrox.books24x7.com