Solved

Problem with quote marks in SQL string (Access VBA)

Posted on 2010-11-13
4
803 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
ID: 34127109
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
ID: 34127146
Try using single quotes, after checking Qlemo's comment.

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

Author Comment

by:colevalleygirl
ID: 34127190
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
ID: 34127222
For the benefit for anyone else reading thus the final working string is:

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

22 Experts available now in Live!

Get 1:1 Help Now