Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 4th, 2003, 11:32 AM
Authorized User
 
Join Date: Dec 2003
Location: , WV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old December 4th, 2003, 11:36 AM
Authorized User
 
Join Date: Jun 2003
Location: , FL, USA.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 4th, 2003, 11:43 AM
Authorized User
 
Join Date: Dec 2003
Location: , WV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 4th, 2003, 02:49 PM
Authorized User
 
Join Date: Dec 2003
Location: , WV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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'
 
Old December 4th, 2003, 03:15 PM
Authorized User
 
Join Date: Jun 2003
Location: Sheridan, OR, USA.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 4th, 2003, 03:46 PM
Authorized User
 
Join Date: Dec 2003
Location: , WV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old December 4th, 2003, 04:10 PM
Authorized User
 
Join Date: Dec 2003
Location: , WV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old December 4th, 2003, 04:19 PM
Authorized User
 
Join Date: Jun 2003
Location: Sheridan, OR, USA.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 4th, 2003, 04:24 PM
Authorized User
 
Join Date: Dec 2003
Location: , WV, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Beginning Access 2002 VBA Chapter 5 Olivia54 BOOK: Beginning Access VBA 2 August 16th, 2004 02:20 PM
Beginning Access 2002 VBA Code pajiyar BOOK: Beginning Access VBA 0 July 12th, 2004 08:04 AM
Access 2002 VBA CD-ROM rmccamont Access VBA 2 May 7th, 2004 09:22 PM
Problem with examples in Access 2002 VBA tcarnahan BOOK: Beginning Access VBA 0 January 26th, 2004 08:14 PM





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