Proper syntax for variables in SQL statement 1

[kellbell]

I am working on an access database where my queries are performing multiple calculations for financial analysis. In order to do these calculations, I have used the DLookup function and assigned the result to a variable and then performed calculations with these variables to get the financial metric I desire. My problem now is writing these metrics to a table.

Here's a short version of the code I'm trying to run:
strSQLCreate ="INSERT INTO tblTotalDealerMetrics([FSID])"&_
"VALUES(lngFSID);"

lngFSID is a variable defined in the code. I know that SQL requires different formats for strings, numbers, etc. but I can't seem to figure out what combination of ' " & to use for a variable. Any ideas?

Thanks in advance!

 

[Remou]

It depends on the field type:

#01/01/01# Date
'abc' text
123 Number

You need a value rather than a variable:

[tt]strSQLCreate ="INSERT INTO tblTotalDealerMetrics([FSID]) " & _
" VALUES(" & lngFSID & ");"[/tt]

 

[kellbell]

Thanks for the quick reply. However, when I use this code and try to run the code Access gives me a message box with the following "Microsoft Access can't find the field '|' referred to in your expression."

I was assuming this was due to some sort of syntax error, since [FSID] matches the field name in the table.

Do I have another problem completely?

Thanks.

 

[PHV]

What is the value of lngFSID at the time the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kellbell]

FSID = 1

 

[Remou]

It can often be helpful to Debug.Print the SQL string.

 

[kellbell]

Thanks for the advice - but I seem to be having problems understanding how Debug.Print works (I'm new to using VBA & Access), as I can't get it to print my SQL string (nothing shows up in the immediate window).

strSQLCreate = "INSERT INTO tblTotalDealerMetrics([FSID]) " & _
                   "VALUES(" & lngFSID & ");"
                   
    Debug.Print "SQL" & strSQLCreate
     
     DoCmd.RunSQL (strSQLCreate)

I tried putting the Debug.Print after the DoCmd.RunSQL as well. What am I doing wrong?

Thanks so much for all your help!

 

[Remou]

A bit like this:

strSQLCreate = "INSERT INTO tblTotalDealerMetrics([FSID]) " & _
                   "VALUES(" & lngFSID & ");"
'Print to immediate window                   
Debug.Print strSQLCreate
'Halt execution for  testing purposes
Stop    
DoCmd.RunSQL strSQLCreate
'Alternative
'CurrentDb.Execute strSQLCreate, dbFailOnError

 

[kellbell]

Just would like to thank you for your help. The reason I was getting that goofy message box was I had a divide by zero error in code previous to the SQL statement. At least I believe that was the problem, since once I got ride of that error my SQL statement worked, and I was able to get the debug.print to work as well. Also a helpful tool which will be used in the future I am sure.

Thanks again for all your quick responses!