'Insert Into' code

[gfcaim]

How do i insert the current date/time into a table? I'm using:

RPT = Me.Combo87.Value
Application.CurrentDb.Execute "INSERT INTO tbl_ReportLog (DateTime, Report) VALUES ('" & Now() & "', " & RPT & ");"

The RPT bit works fine but i cannot get the other field!

 

[DreamGenius]

Now() vs Date

I believe Now() is more application level, in that it requires the syntax =now() in Excel and Access.

I'll be corrected if I'm wrong, no doubt, but I think you want to replace Now() with Date, being the VBA equivalent.
__________
Alan
Learning Access, fast!

 

[The Stoat]

How do i insert the current date/time into a table? I'm using:

RPT = Me.Combo87.Value
Application.CurrentDb.Execute "INSERT INTO tbl_ReportLog (DateTime, Report) VALUES ('" & Now() & "', " & RPT & ");"

The RPT bit works fine but i cannot get the other field!

Hi

Try

Application.CurrentDb.Execute "INSERT INTO tbl_ReportLog (DateTime, Report) VALUES (#" & Now() & "#, " & RPT & ");"

HTH

TS

 

[Bat17]

as "now" is a date field you will probably need to wrap it in hash marks rather than quotes.

peter

 

[gfcaim]

nope - it dont like that either, although the suggestion makes sense. I used hashes but no joy - no error message, just nothing!

 

[Bat17]

I think that you may be falling foul of American dates!
try
"INSERT INTO tbl_ReportLog (DateTime, Report) VALUES (#" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#, " & rpt & ");"

I assume that RPT is numeric?

Peter

 

[gfcaim]

thanks for the reply - i copied and pasted your line but it is exactly the same - no joy.

RPT is numeric

 

[Bat17]

I had a better look this time
turns out that datetime is a reserved word in SQL so wrap it in []
"INSERT INTO tbl_ReportLog ([DateTime], Report) VALUES (#" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#, " & rpt & ");"

Peter

 

[gfcaim]

Bat17, I love you and want to have your babies!

Thanks a lot for your persistent help. I renamed my field 'DT' and everything is now tickety-boo.

Thanks again.