Save NOW() in an SQL INSERT

[johnain]

Hi all

I really have hunted for this first! Surprisingly I havn't found a solution.

I want to save NOW() (i.e. Date and time) into an event log file. But I just cannot work out the syntax. My insert statement works fine without the date field in, but fails on a syntax error (3134) when I include it.

strsqlac = "INSERT INTO EventLog ( EventTime, User, EventType, EventMessage, DocRef, AutoSeq, CoCode ) " & _
" Values ( '" & Now() & "', '" & GlobUser & _
"', '" & Mess2 & _
"', '" & Mess1 & _
"', '" & Docref & _
"', " & AutoSeq & _
", '" & CoCode & _
"' );"

... it's the first column, EventTime that is the issue. I have tried several different ways of wrapping it in the VALUES () without success.
My database field EventTime is defined as a General date which Access 2007 tells me will give me date and time. That's what I want.

The Value formats I have tried (currently showing as 'NOW()' above, are ...

#NOW()#
#'NOW()'=
'#NOW()#'
and (as I say) 'NOW()'

I picked all of these suggestions up by Googling and looking on here, but not none of them work for me.

Should I perhaps do a string conversion and save that? It seems daft to have to.

 

[spikepl]

" Values ( Now() " & ", '" & GlobUser & _

Check what you are doing: the way to do that is debug.printing the SQL string , inspecting it and stuffing it into the SQL view of the query designer for testing.

Follow http://www.baldyweb.com/immediatewindow.htm

 

[johnain]

Hi Spike. Thanks for coming back.

The output in the watch window looks ok . Here is the version with the hero marks

( '16/10/2014 11:26:02', 'JohnAin', 'New Document Created', 'Your document 21-2014 was saved with AutoSequence 1019.', '21', 1019, '100' )

And my other versions "look" ok to me as well ... i.e. with the ...

#'16/10/2014 11:26:02'#,
'#16/10/2014 11:26:02#',
#16/10/2014 11:26:02#,

as the other alternatives. They all look a faithful reproduction of what I expected to see, but Access doesn't like them

It's a General Date in the table, which is exactly what I want.

The rest of the SQL is fine. I took out the field and the VALUE() and the insert works .. so it is definitely this column that it doesn't like.

I like your query designer suggestion. Here I go with that.

 

[namliam]

Problem is 16/10, is euro format, SQL requires MM/DD US format...

You should be fine per Spike's suggestion of "hardcoding" the now()
" Values ( Now() ", '" & GlobUser & _

Or by forcing the ## notation using the us format
" Values ( #" & Format(Now(), "MM/DD/YYYY HH:MM:SS") & "#, '" & GlobUser & _

 

[johnain]

Problem is 16/10, is euro format, SQL requires MM/DD US format...

You should be fine per Spike's suggestion of "hardcoding" the now()
" Values ( Now() ", '" & GlobUser & _

Or by forcing the ## notation using the us format
" Values ( #" & Format(Now(), "MM/DD/YYYY HH:MM:SS") & "#, '" & GlobUser & _

Hi NamLiam. That worked an absolute treat. Thanks a million. Dates really are always an issue in every language I work in, or have worked in.

 

[namliam]

Credits go to Spike, he already did fix it for you, you just didnt quite get his answer