Access VBA: If Then Statement involving dates doesn't work

[cleteh]

In Access 1997 the following code errors out on the line in red

Private Sub Form_Open(Cancel As Integer)
If [SystemDate].max[SystemDate] = Date() Then
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO SystemDate (SystemDate) VALUES (Date());"
DoCmd.SetWarnings True
End If
End Sub

I am checking if the most recent date in a table matches today's date... if it does do nothing. Else insert today's date into the table. It I remove the first 2 lines it does enter today's date into the table.

[jonh]

that's not vba. where did you copy it from?

[cleteh]

Just getting back into this project.. someone suggested I code that way to check the most recent date doesn't equal today's date

[jonh]

You can't reference a field from a table like that.
You either need to open a recordset or use dlookup().

[cleteh]

Not working but i think im getting closer... not sure how to call the most recent date with dlookup

Private Sub Form_Open(Cancel As Integer)
Dim maxDate As Date
maxDate = DLookup("SystemDate", "SystemDate", "Criteria = MAX(SystemDate)")
If maxDate = Date Then
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO SystemDate (SystemDate) VALUES (Date());"
DoCmd.SetWarnings True
End If
End Sub

[cleteh]

Private Sub Form_Open(Cancel As Integer)
Dim maxDate As Date
maxDate = DMax("SystemDate", "SystemDate")
If maxDate = Date Then
Else
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO SystemDate (SystemDate) VALUES (Date());"
DoCmd.SetWarnings True
End If
End Sub