By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Invalid Qualifier In VBA SQL Query

MindBender77
100+
P: 234
Hello All,
I keep getting an "invalid qualifier" error while trying to execute the following SQL statement from a form.
Expand|Select|Wrap|Line Numbers
  1. strSQL="Insert into TableA ( data1, data2, data3 ) values(@" & txt1 & "@,@" & txt2 & "@,@" & txt3 & "@);"
  2.  
  3. strSQL=replace(strSQL,"@", chr(34))
  4. currentdb.execute strSQL, dbfailonerror
  5.  
Bold text represents where the error occurs. The syntax appears to be ok but, it fails during compiling.

Any suggestions or insight would be helpful,
Bender
Jan 8 '09 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
Hi Bender. Your syntax works just fine for me on a test form, and inserts three text values into a table regardless ofthe presence of single apostrophes (or not) in the text (as per a previous post involving this that led to the use of the replacable '@'s in your syntax, to keep the double quote syntax correct)...

Could you post the contents of the SQL string that is failing?

Be aware that if any of the text values in your string contain '@'s (e-mail addresses, for example) these would be incorrectly replaced with double quotes, leading once again to premature ending of the SQL string and a syntax error. If this is the case then substitute any other little-used character for the '@' in the code above - a tilda for example ('~').

-Stewart
Jan 8 '09 #2

MindBender77
100+
P: 234
Thanks for your reply, Stewart. Here is an outline of the table i'm trying to append too. Note, I've ran this using a module during testing and no errors were produced.

TableA
Description - Text - ex. "Tylenol"
NDC - Text - ex. "55555444422"
UPCScan - Text - ex. "5555544422"
HostOrderPerSku - Number - ex. 30
Manufacturer - Text - ex. "Pfizer"
BO_Avail - Number - 600


I've tried removing the @ from the number fields with no success. My last resort is to create a module and run this from a macro.

Bender
Jan 8 '09 #3

Expert Mod 2.5K+
P: 2,545
One other thing to try is to replace the Execute method with

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.RunSQL strSQL
  3. DoCmd.SetWarnings True
and see if you still get a failure.

I have found that on occasion Execute fails whereas RunSQL does not - even though both should work identically from an SQL perspective.

If RunSQL also fails, it suggests that the SQL string is not correctly formed. You should then breakpoint your code and examine what the string is set to when the failure occurs.

-Stewart

ps I'm assuming that you are running the code from a suitable event - the on-click event handler of a command button for instance. You are not simply trying to place the code into a form's code module without a suitable Sub or Function call to wrap it by any chance?? Unlikely I know, but I have to eliminate this remote possibility.
Jan 8 '09 #4

MindBender77
100+
P: 234
Stewart,
I have also experienced where runsql works but, the other produces an error. This is one of those times. So I did the following.

I shutdown the DB and brought it back up and added the code to a module; created the macro; and called it from the command button on the original form. Voila it works.

I've had instances in the past where bouncing a DB is a "magic" fix for code that appears fine but, produces errors.

Thanks for your help and do take care,
Bender
Jan 8 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.