Find greatest value with VBA

[Guest]

I have a form with a 5x8 grid of 40 different text boxes, each of which could
have a date in it, or it could be blank. Each row represents a phase of a
program, so the grid looks like this:

A1 A2 A3 A4 A5
B1 B2 B3 B4 B5
C1 C2 C3 C4 C5
D1 D2 D3 D4 D5
E1 E2 E3 E4 E5
F1 F2 F3 F4 F5
G1 G2 G3 G4 G5
H1 H2 H3 H4 H5

I have another text box in the form that is supposed to reflect the Current
Phase. The Current Phase is just the row with the most recent date... so if
the most recent date were in G4, then the current phase would be G. What I
need to figure out is some way for the Current Phase field to automatically
be populated by checking for the most recent date in all the fields, and then
entering the corresponding phase. I'm thinking something like:

If [A1] > [All other fields] Then
Me.CurrentPhase = Phase A

And repeating that for all of the text boxes. I've tried a few things but
can't get anything to work. Does anyone have any ideas? Thanks in advance.

 

[Guest]

Tim,

This is untested "air" code and may take some tweeking, but the basic logic
is good:
Function GetHighDate(dtmBaseDate as Date) As Date
Dim ctl As Control
Dim frm As Form
Dim dtmHighDate

dtmHighDate = Null
Set frm = Forms.MyFormName
With frm
For Each ctl In frm
If ctl.ControlTye = acTextBox Then
if ctl.Value > dtmHighDate
dtmHighDate = ctl.Value
End If
Next
End With
GetHighDate = dtmHighDate
End Function