Using SQL Insert into with a variable

erichoangnguyen · April 25th, 2005, 01:46 PM

Hi all,

I have the following simple statements below. I kept getting a dialog box required to enter in a value for the variable START. How do I use the SQL Insert into with a variable?

    start = Now()

    StrSQL = "INSERT INTO log (start_date) VALUES (start);"
    DoCmd.RunSQL StrSQL

Appreciate for your help. Thanks.

BrianWren · April 25th, 2005, 04:45 PM

Change to:

Code:

    start = Now()  

    StrSQL = "INSERT INTO log (start_date) VALUES  (" & start & ")"
    DoCmd.RunSQL StrSQL

You might need to format start a bit to get it to go into a date-type field. the example I have creates SQL like

âINSERT INTO tbl (fld1, date_fld)
VALUES ('Hey', TO_DATE('FEB 3, 2003', 'MON DD, YYYY');â

(When entering SQL into SQL-Plus for Oracle, the SQL must be terminated with a â;â but in code, when building an SQL string, the â;â generates a syntax error.)

Access uses â#âs to delineate dates, so perhaps this will work for you

Code:

    start = Now()  

    StrSQL = "INSERT INTO log (start_date) VALUES  (#" & Format(start, "mm/dd/yyyy") & "#)"
    DoCmd.RunSQL StrSQL

What you posted causes Accessâ SQL interpreter to try to assess a variable in the SQL named start, whereas what you are trying to do is to feed a VBA variable into the string that will be sent to the SQL interpreter.

rjweers · April 30th, 2005, 08:07 AM

It seems that you definitely want the time as part of your value. So you'll want to avoid any formatting.

If "start_date" is defined as a date field in "log" and "start" is DIM-med as a "Date" in your procedure, use something similar to Brian's last suggestion:

StrSQL = "INSERT INTO log (start_date) VALUES (#" & start & "#);"

Or you can skip DIM-ming "start" and just go to:

StrSQL = "INSERT INTO log (start_date) VALUES (#" & Now() & "#);"

The next question you'll ask is "how do I keep the 'Warning you are about to insert...' message from showing?". So... the short form of the whole looks like this:

Code:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO log (start_date) VALUES  (#" & Now() & "#);"
DoCmd.SetWarnings True

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

erichoangnguyen · May 3rd, 2005, 01:40 PM

Problem solved thanks to Brian & RJ!!!

here is my final statements:

   start = Now()

    StrSQL = "INSERT INTO log (start_date) VALUES ( #" & start & "#);"
    DoCmd.RunSQL StrSQL

In my table, I then set my column start_date as date. The symbol # is required to indicate date values . I want to capture the complete date value including time value so I did not use any formatting.
cheers !!!