SQL Insert Into question

[garyholc]

I want to insert some values into a table but cant get the syntax right. I have four fields in the table:

Date, Time, User, Action

When a user logs in, I want it to insert the date, time, the username and action performed. The username I get from another script.

So I declare the variables:

Dim dateis, timeis, useris, actionis

dateis = date ' this is a date field
timeis = time ' this is a time field
useris = username ' text field
actionis = "User has logged in"


Then do the insert bit:

docmd.runsql "INSERT INTO T_Audit (Date,Time,User,Action) VALUES (dateis,timeis,useris,actionis);"

But keep getting syntax error. I know its something to do with the VALUES section, have tried quotes etc but cant get it right.... help!

 

[Kiwiman]

Howzit

Try

docmd.runsql "INSERT INTO T_Audit (Date,Time,User,Action) VALUES (#" & dateis & "#,#" & timeis & "#,'" & useris & "','" & actionis & "');"

When using variables in your sql script, follow these rules

Dates and times wrapped in #
Text wrapped in '
Numbers not wrapped.

I would alos avoid using Reserved words as field \ talbe names - somewhere down the line it will get rather confusion. Do not use words such as Date \ Time etc

 

[gemma-the-husky]

well having fields called date time (possibly user) is not advised. these are reserved words in access, and will not work correctly.

rather than using your fields date retrieves todays date, and time retrieves the current time

to use your field you have to use square brackets [date], or [time]

but it would be better to change the field names

 

[garyholc]

Thanks both , got it to work with your help.

Point taken about field names, will look to change them.