Avatar of colevalleygirl
colevalleygirlFlag for United Kingdom of Great Britain and Northern Ireland asked on

Problem with quote marks in SQL string (Access VBA)

I have a problem getting the quote marks right in a SQL string in Access VBA. The string I currently have is as follows

CurrentDb.Execute "INSERT INTO " & mcstrGroupTable & " (Group) VALUES ("""" & JetSQLFixup(pstrname) & """");"

Open in new window


where mcstrGroupTable = "tblGroup" and pstrName is a string that can contain any text (including any number of single and double quote marks).

This gives me a "syntax error in the SQL statement" when executed, even when pstrName contains no quote marks at all.

JetSQLFixUp is a helper routine that does the following.

    
    strTemp= Replace(pstrname, "'", "''")
    strTemp = Replace(strTemp, "|", "' & chr(124) & '")
    JetSQLFixup = Replace(strTemp, """", """""")

Open in new window


tblGroup consists of two fields: An Autonumber field GroupID which is the primary key, and a text Field Group.

No doubt the answer will be screamingly obvious but I'm going VBA-blind right now.
Microsoft AccessSQL

Avatar of undefined
Last Comment
colevalleygirl

8/22/2022 - Mon
SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
colevalleygirl

Working from both suggestions, I've got the following to work -- almost:

"INSERT INTO tblGroup (Group) VALUES (" & """" & JetSQLFixup(pstrName) & """" & ");"

It give a SQL string: INSERT INTO tblGroup (Group) VALUES ("sometext");

However, it still doesn't execute so there's something else wrong. That might be another question however!
ASKER
colevalleygirl

For the benefit for anyone else reading thus the final working string is:

INSERT INTO tblGroup ([Group]) VALUES (" & """" & JetSQLFixup(pstrName) & """" & ");
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23