p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Access 2002 VBA with SQL using a variable (http://p2p.wrox.com/showthread.php?t=6874)

cpopham December 4th, 2003 11:32 AM

Access 2002 VBA with SQL using a variable
 
Hello everyone,

I am new to this forum. I can not seem to find an answer anywhere. I know that you can use a variable in vba with sql, however, this is my code:

Option Compare Database
Option Explicit

Dim strSaveAs As String
Dim strSQL As String

' Set SQL variable to SQL statement
     strSQL = "INSERT INTO TblAccessDate ([Date],[TableName])" & _
              "VALUES (Date(), ' & strSaveAs & ');"
' Run SQL Statement
DoCmd.RunSQL strSQL

Okay the statement part to insert a date into the table works fine, but I can not get the INSERT INTO statement to pass the variable to the table also. I am basically using the table to see what date and what file was created when a user runs this code. I have tried a few different way of trying to accomplish this. Any ideas???

Thanks, Chester

Kenny Alligood December 4th, 2003 11:36 AM

Since you are trying to pass a string you need to include some double quotes. Try this for your SQL string...

"VALUES (Date(), '" & strSaveAs & "');"

Kenny Alligood

cpopham December 4th, 2003 11:43 AM

Thanks,
I tried it that way, but the single quote behind the double quote made it a comment and gave me a syntax error.

I have also tried:
 "VALUES (Date(), " & strSaveAs & ");"

But it still will not pass the variable to through the INSERT INTO statement.

Chester

cpopham December 4th, 2003 02:49 PM

When I try double quotes in single quotes like this;
 "VALUES (Date(), '" & strSaveAs & "');"

I get this error message:
Invalid SQL Statement; 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

John K. King December 4th, 2003 03:15 PM

Hi:


The additional double quotes were necessary, but there are a couple of other things:

1) date() is a vb function, SQL doesn't know what to do with it. Pass the date in as a value

2) Insert a space prior to the 'VALUES' keyword, otherwise the two lines run together. It's easy to lose track of that type of thing when using line continuations. That's probably where the syntax problem is coming from.


So, try
" VALUES (#" & CStr(Date()) & "#, '" & strSaveAs & "');"


I think that will do it for you.

JK

cpopham December 4th, 2003 03:46 PM

That worked great. Thanks for the help. :D One question though,
In my original code when I had the date as date() it inserted the date fine into the table, how come it worked that way, but after changine the SQL around it would not work the same?

Thanks agian, Chester


cpopham December 4th, 2003 04:10 PM

Okay got the time figured out also thanks for all the help. Somethings you think are simple and a couple of little porblems in the code will drive you nuts!!! :D



John K. King December 4th, 2003 04:19 PM

I may have been too long away from Access (I work with SQL Server). It looks like Date() is a legitimate function in a SQL statement from within Access.

The best way to answer your question is to

1) add the line

Debug.Print strSQL

to the procedure after the point where you set the variable strSQL

2) modify the procedure to set the variable strSQL to the original string, step through it, and see the SQL statement printed in the immediate window.

3) modify the procedure to set the variable strSQL to the string where Date() failed, step through it, and see the SQL statement printed in the immediate window.

You may be able to identify the reason for the failure when comparing the two strings. If not, just post the results and I'll see if I can see the reason.

To be honest, I don't know why it didn't work when you first added the double-quotes. I would have expected

"VALUES (Date(), '" & strSaveAs & "');"

to work fine. It may have been that the missing space (separating the closing parenthesis and the 'VALUES' keyword) was the problem then.

JK

cpopham December 4th, 2003 04:24 PM

Okay thanks, either way, my goal is accomplished!!!:D



All times are GMT -4. The time now is 12:19 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.