Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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'
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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

Reply With Quote
  #7 (permalink)  
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


Reply With Quote
  #8 (permalink)  
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
Reply With Quote
  #9 (permalink)  
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:58 AM.


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