Invalid Qualifier In VBA SQL Query

[MindBender77]

Hello All,
I keep getting an "invalid qualifier" error while trying to execute the following SQL statement from a form.

strSQL="Insert into TableA ( data1, data2, data3 ) values(@" & txt1 & "@,@" & txt2 & "@,@" & txt3 & "@);"

strSQL=replace(strSQL,"@", chr(34))
currentdb.execute [B]strSQL[/B], dbfailonerror

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

[Stewart Ross]

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

[MindBender77]

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. "5555544442 2"
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

[Stewart Ross]

One other thing to try is to replace the Execute method with

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
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.

[MindBender77]

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