Error in IF Statement when checking reoord

[cleteh]

This line of code works DoCmd.RunSQL "INSERT INTO SystemDate (SystemDate) VALUES (Date());"

Its inserting todays date into the Table SystemDate in the field also titled SystemDate. The Code is in the Form Open procedure.

Now, Im trying to build an if statement that will check to see IF todays date has already been entered THEN do nothing ELSE enter today's date.

Im getting an error on line 1, Here is my Code:

If [SystemDate].MAX[SystemDate] = Date() Then
Else
DoCmd.RunSQL "INSERT INTO SystemDate (SystemDate) VALUES (Date());"
End If

I get a compile error Expected: Then or GoTo

[jonh]

Get a recordset
check if it returns the record



Const SQL_SELECT As String = "select systemdate from systemdate where systemdate=date()"
Const SQL_INSERT As String = "insert into systemdate (systemdate) values (date())"



With CurrentDb
If .OpenRecordset(SQL_SELECT).EOF Then
.Execute SQL_INSERT
Debug.Print "record added"
Else
Debug.Print "record not added"
End If
End With


If the field doesn't accept duplicates just check if the record was added or not



Private sub example()
With CurrentDb
.Execute SQL_INSERT
If .RecordsAffected Then
Debug.Print "record added"
Else
Debug.Print "record not added"
End If
End With
End sub


of course there might be other reasons why the record wasn't added so instead of RecordsAffected you could check the error



On Error Resume Next
.Execute SQL_INSERT, dbFailOnError


Select Case Err.Number
Case 0
Debug.Print "record added"
Case 3022 'duplicated index
Debug.Print "record not added"
Case Else
Debug.Print Err.Number, Err.Description
End Select