Insert SQL command with Datetime in MS-Access

Question

I am trying the following query in MS-Access 2007, but it fails on the time field.

INSERT INTO LOG (
    EMPLOYEECODE, STATUSID, LOCATIONID, TIME, DURATION,
    SHIFTID, LATECOMING, EARLYGOING, LOGDATE, STATIONID
)
VALUES (
    1, 1, 0, '4/21/2009 2:25:53 PM', 0, 
    8, 0, 1, '1/1/2009', 1
)

The TIME field is defined as a datetime.

Without the TIME field, the query works fine!

I've tried a number of different things, such as enclosing the datetime in hashes, quotes etc. However, the query still fails on the time field.

---

Thank you guys! That almost got me fully there. I still kept getting the syntax error for the insert statement, but then on further googling, I realized that TIME might be a reserved keyword, so putting it on box brackets as [TIME] worked!

Answer 1

Date & Time input in access use #, since access can't do auto conversion from char/text into date or time in SQL Query (or access call it query), and you better use international standard for inputting date time which was YYYY-MM-DD HH:NN:SS (4-digit year, 2-digit month, 2-digit day, 2-digit hour, 2-digit minute, 2-digit second)

so for 4/21/2009 2:25:53 PM use #2009-04-21 14:25:53#

or if it still fail, you can use #'2009-04-21 14:25:53'#

Edit: Above might be working if you enable ANSI 92 or using ADO/OLEDB as database interface, thanks David for pointing out

I suggest you use YYYY-MM-DD HH:NN:SS format and try it with single quotes (') before use # like i said above