SQL INSERT INTO Date Syntax Error

[tuna]

I'm trying to run the following SQL (PropertyID is Double, TimeStamp is Date/Time) but the TimeStamp part is causing a syntax error (Runtime error 3134) - am I missing something?

DoCmd.RunSQL "INSERT INTO tblProperties (PropertyID, TimeStamp) VALUES (1, #07/08/2010 06:37:43#)"

I've also tried with quotes:

DoCmd.RunSQL "INSERT INTO tblProperties (PropertyID, TimeStamp) VALUES (1, #'07/08/2010 06:37:43'#)"

Ideally, I would like to have somthing like Now() or dteDateVar in place of the static time in the end - I was just testing with the static date but failed at the first hurdle!

Thanks very much

Edit: Also, adding a semicolon at the end doesn't help

 

[DCrake]

Try

DoCmd.RunSQL "INSERT INTO tblProperties  Set PropertyID = 1 , TimeStamp = " & Now() & ";"

 

[JANR]

This should work:

DoCmd.RunSQL "INSERT INTO tblProperties (PropertyID, [TimeStamp]) SELECT 1, Now()"

JR

 

[JANR]

I had a thought, the reason this fails:

DoCmd.RunSQL "INSERT INTO tblProperties (PropertyID, TimeStamp) VALUES (1, #07/08/2010 06:37:43#)"

Is the name TimeStamp, it is a reserved word and must be enclosed in square brackets.

DoCmd.RunSQL "INSERT INTO tblProperties (PropertyID, [B][SIZE=3][COLOR=#ff0000][[/COLOR][/SIZE][/B]TimeStamp[B][SIZE=3][COLOR=red]][/COLOR][/SIZE][/B]) VALUES (1, #07/08/2010 06:37:43#)"

I ran into the same problem yesterday when linking to a thirdparty application and the had a fieldname of MOD, an abbriviation of Model

JR