Solved

Problem with quote marks in SQL string (Access VBA)

Posted on 2010-11-13
4
789 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:colevalleygirl
  • 2
4 Comments
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 125 total points
Comment Utility
Your 4 double quotes need to be 2 only:
CurrentDb.Execute "INSERT INTO " & mcstrGroupTable & " (Group) VALUES ("" & JetSQLFixup(pstrname) & "");"

Open in new window

0
 
LVL 30

Accepted Solution

by:
hnasr earned 125 total points
Comment Utility
Try using single quotes, after checking Qlemo's comment.

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

Author Comment

by:colevalleygirl
Comment Utility
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!
0
 

Author Comment

by:colevalleygirl
Comment Utility
For the benefit for anyone else reading thus the final working string is:

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now