Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Problem with quote marks in SQL string (Access VBA)

Avatar of colevalleygirl
colevalleygirlFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessSQL
4 Comments1 Solution908 ViewsLast Modified:
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) & """");"

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, """", """""")

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.
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed NasrFlag of Oman imageRetired IT Professional

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answers