Insert Date in query problem

[beefwellington]

Dim sql As String

        sql = "INSERT INTO EquipmentLog (EquipmentID, State, User, Comment, Status, DateTime) VALUES ('" & Me.EquipmentID.Value & "', '" & Me.cboNewState.Value & "', '" & Me.cboUser.Value & "', '" & Me.Comment.Value & "', '" & Me.Status.Value & "', #" & Now() & "#)"
        
        With DoCmd
            .SetWarnings False
            .RunSQL sql
            .SetWarnings True
        End With

This is what I currently have but when I click a button on the form to save, I get a VBA error stating the INSERT INTO syntax is incorrect. I noticed if I changed my sql String to:

sql = "INSERT INTO EquipmentLog (EquipmentID, State, User, Comment, Status) VALUES ('" & Me.EquipmentID.Value & "', '" & Me.cboNewState.Value & "', '" & Me.cboUser.Value & "', '" & Me.Comment.Value & "', '" & Me.Status.Value & "')"

The INSERT INTO query works fine. But I need to time stamp the INSERT. The "DateTime" field name is in my Table as a "Date/Time" type. Am I missing something in my code? It seems like it should work fine but obviously it doesn't. Any help would be appreciated.

 

[pbaldy]

Add this after the string is built:

Debug.Print sql

which will let you examine the finished SQL in the VBA Immediate window. If that doesn't lead you to the solution, post the SQL here.

 

[boblarson]

Also, according to this, by Allen Browne, DateTime is an Access Reserved Word and therefore may be causing you grief.

 

[beefwellington]

"Debug.print sql" I will have to remember to use in the future.

boblarson: You were right it was a reserved word so I renamed DateTime to DateTimeLogged and it works fine now. That's the first time I made that mistake.