varable in sql? 1

[lora]

is it possible to use a variable in an sql statement? something like:<br>
<br>
dbs.Execute &quot;SELECT RegSum1.TMCode , &quot; _<br>
& &quot;field AS VARIABLETotRec, &quot; _<br>
& &quot;[TotOdue]/[TotRec] AS VARIABLEOduePcnt INTO&quot; _<br>
& &quot;[VARIABLEsum1] FROM RegSum1; <br>
<br>
what i've tried hasn't worked; if this just isn't possible i need to move on and just create individual ones for each case.<br>
<br>
THANKS!<br>

 

[DougP]

If you want to use a variable you need to end the string and concatenate the variable with the ampersand<br>
Im not sure I follow which are variables and which<br>
dbs.Execute "SELECT RegSum1.TMCode , field AS VARIABLETotRec, [TotOdue]/[TotRec] AS VARIABLEOduePcnt INTO [VARIABLEsum1] FROM RegSum1; <br>
for one thing you need spaces in between things<br>
It might be easier if you build the SQL statement on one huge line first to get it right.<br>
<br>
Here is a simple one<br>
<br>
Dim SQL as string<br>
SQL= "Select * From Employee Where ID = " & Me!Employee & " SSNum = " & xyz<br>
<br>
Also use the queries TAB to create a query the cut and paste the SQL code it creates in your code<br>
(click the SQL option in the Design view or the query)<br>
I let Access do the work for me<br>
You can with practice get fields off of a form and more using the build option.<br>
<br>
Always set the SQL string to a variable so you can see it in Debug.print<br>
I purposely stop the program on the SQL statement so I can check it (using the F9 key so it makes a Red line )<br>
<br>
Use debug.print after the SQL to see if the syntax is correct i.e. spaces etc.<br>
Note if the variable is a string you must use single quotes around it like so<br>
Look after the Where ID = <br>
And before the SSNum<br>
<br>
SQL= "Select * From Employee Where ID = '" & Me!Employee & "' SSNum = " & MyVariable<br>
Make sure you put spaces in so that Access can tell what it is <br>
<br>
Hope this helps<br>
DougP<br>