Escape Double Quotes
By Richard Rost
3 years ago
Escape Double Quotes for SQL Insert Statement
In this Microsoft Access tutorial, you will learn how to properly handle and escape double quotes to prevent errors in your SQL statements.
Alex from Jersey City, New Jersey (a Platinum Member) asks: I'm using the technique that you taught us in your Save Notes video, where you can take the current notes on the customer form and archive them by saving them in the contact table, and it works great. However, it encounters a runtime error 3075 syntax error (missing operator) in query expression, and breaks the code if there are double quotes in the notes field. How can I fix that?
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, escape double quotes, SQL insert, Escape double quotes, escape quotes, escaping quotes, run-time error 3075, syntax error (missing operator) in query expression
Intro In this video, I will show you how to escape double quotes in SQL INSERT statements using Microsoft Access VBA. We will talk about the common runtime error that happens when quotes appear in text fields, and I will demonstrate how to handle this issue by replacing double quotes with double double quotes in your VBA code. You'll see step-by-step how to archive notes from a form to a contact history table and avoid errors caused by quotation marks in your data.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, we're going to learn how to escape double quotes in your SQL INSERT statements in Microsoft Access. What does all that mean? Well, we're going to find out in just a minute.
Today's question comes from Alex in Jersey City, New Jersey, one of my Platinum members. Alex says, "I'm using the technique that you taught us in your Saved Notes video, where you can take the current notes on the customer form and archive them by saving them in the contact table, and it works great. However, it encounters a runtime error 3075 (syntax error: missing operator in query expression) and breaks the code if there are double quotes in the notes field. How can I fix that?"
Alex, you are running into the old double double quotes problem. I've done a couple of different videos on this one. Let me run through how to fix it real quick, but first:
For everyone else, if you haven't watched this video, go watch it. Basically, it's where you've got a Notes field on your customer form and you want to add more to it, but you don't want to keep adding and adding notes because eventually, that field is going to get really, really big. So what you can do is take the old notes that are in there and archive them to your contacts table. You've got a history—you are not losing them, but they may not be the most current notes. That's archiving your notes, saving your notes.
So, in this video, I show you how to make a little button. You click the button, it takes what's up here and drops it down into the contact history, basically. Then you can type different notes up here.
Go watch this first so you understand what I'm talking about.
Both of these are developer level videos, which means we're going to use some VBA programming. If you've never done any VBA programming before, go watch this video. It's about 20 minutes long. It'll teach you everything you need to know to get started.
Go watch my video on variables if you've never used those before.
Watch my video on the Replace function.
Also, watch my older video on concatenation, where I talk about using single quotes, double quotes, double double quotes, and so on.
Optionally, watch this video where I talk about using the double double quotes and not the single quotes.
All of these are free videos. They're on my YouTube channel and on my website. Go watch those, then come on back.
Here I am in my TechHelp free template. This is a free database you can download off my website if you want to. As a quick recap of what I covered in the Saved Notes field video: here is the Notes field, and here is my contact history. Normally, you come in here and say something like "we talked about [whatever]," and you have a history of all the things you talked about.
Let's say these notes here aren't particularly relevant anymore. You want to replace this with something else, but you don't want to lose the old notes. You don't want to have to cut this, open the contact history, click in there, paste, then come back out again. It would be nice to have a single button to do that, and it's very easy to do.
This is the focus of my previous video. I'll just copy and paste that button. We'll call this "archive" or "archive the notes" or whatever you want to call it. That was a Shift+Enter inside there when you type "archive" and then Shift+Enter to give you another line inside your button caption.
Let's give this button a good name—we'll call this the "Archive" or "ArchiveBtn".
I'm going to right-click "Build Event." That'll bring up my VBA code editor. Let me move this down a bit so you can see it all right. We're inside the archive button click event.
In here, we're just going to write a simple little Execute statement:
CurrentDB.Execute
I'm going to INSERT INTO—this is my append query, right? It's:
INSERT INTO contactT
What are the fields in contactT? CustomerID, Description, and Notes.
Now, we're not pulling off a table; we're just going to insert some values off the form. So it's going to be VALUES, and then in parentheses, our three things.
If it's CustomerID, let's say it's 5. Then for Description, I'm going to put just the words "Archive Notes," and then the Notes field.
So for the Description, the actual text is "Archive Notes." For the Notes field, it would be whatever's in the Notes field. So I just put in here "Notes".
Close that up, press Enter.
In here, we've got string values, so these have to normally be double double quotes.
If I want to put the actual word "Notes" in there, I would do it like that, but I want to replace the 5 with the CustomerID, and I want to replace "Notes" with the actual Notes field.
Yes, when I write these statements, sometimes I'll actually just type in bogus values and then go back and fix them, because it's kind of easier sometimes to think that way.
So, I want to replace that 5 right there with the actual CustomerID on the form. In this case, it's going to be:
CustomerID
Ampersand CustomerID ampersand, to start the string back up again.
"Archive Notes" is going to be just that—the words "Archive Notes"—so that can stay just like it is.
The Notes field is going to be replaced with—remember: close the string, notes, and open the string again, because you have to have double double quotes inside the string.
So that works. There's your double double quote issue. Then, when you're done with that, we can say:
Notes = Null (we'll blank the Notes field), and then maybe Notes.SetFocus to put the focus back there.
If you want, you can beep or pop up a message box saying it's done.
That's all you need right there. That's a brief overview of what we covered in the other video.
Save it. Always throw in a Debug Compile from time to time. Come back out here, close, and open it up again.
We'll put in here "Here are my old contacts," and I'll click the button.
Let's go check and make sure they're in there. Right here—Archive Notes—here are my old contacts.
Remember, the notes go down here in the bottom. These are the descriptions.
More contact info. The point behind this is, you come back to this customer a while later in the future, and there's more contact info. This note isn't valid anymore. Here is some new stuff. So I'm going to type him an archive and say, "Here's more stuff," and then we move on to the next guy: Bob. Okay.
Here's where the problem comes in.
I'll come in here and type in: Rick said "there are no more jelly beans," like that. There are quotes in there. When I try to archive that, there's your error message: it says syntax error in query expression Rick said (see the quotes in there?)
The problem is these quotes inside the field are messing up this thing, because your set of quotes actually goes in here and closes that string—it messes up the whole SQL statement.
There are two ways you can fix this. You can either rewrite this as a Recordset, which in some ways is actually better but takes more lines of code, but we're not going to do that today. Or, you can escape those characters.
What does it mean to "escape the characters"? It means to translate those characters into something else. Websites do this all the time because you can't send some characters through the query string of a web browser—you have to escape them; you have to change them into something else. Or, if you know HTML, you escape a space character with the "nbsp" (non-breaking space), or whatever. That's called escaping it. Basically, it's translating one character into another safe character that can be processed.
In this particular case, all we have to do is escape a single double quote with another double double quote. So, you end up getting double double double quotes. It's weird, but that's how it works.
To do that, we'll just throw this into a temporary variable and then turn every set of double quotes into double double quotes.
We'll do it just like this:
Dim S As String
S = Notes
S = Replace(Notes, """", """""")
Make sense? I know it's crazy. So, we're going to replace that with that.
Again, I refer you back to why I prefer to use double double quotes instead of single quotes in a previous video that I mentioned earlier, because it's all kinds of crazy with names and different stuff.
If you're working with SQL Server, then you've got a different problem, but this is for Access.
Once you've done that, you come over here, hit the button—oh, hang on. I'm leaving this error in the video on purpose. Hit Debug. Look what I forgot to do! I did the work up here, but it's still "Notes" here. I forgot to change that. Stop. S goes here now. That's right; we're putting Notes into S, fixing it, now that's going to be S. I guarantee one of you will make that mistake.
Save it again, throw on a Debug Compile, come over here, and hit the button.
There we go. Check the contacts, and there they are, right there—they go in nicely.
That's just, again, a double double quote problem. I see the double double quote problem all over the place, and that's why I do multiple videos on it, because it's a stickler for a lot of people.
If you like learning this kind of stuff, if you enjoy my teaching, then come on over to my website and check out my developer lessons. I've got lots and lots of different ways you can learn with me.
I've got 40-some different levels of VBA classes and all kinds of cool stuff. I'll put a link to this down below—come check it out.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main problem Alex is experiencing when trying to archive notes in Microsoft Access?
A. The notes are not saving to the correct table
B. A syntax error occurs due to double quotes in the notes field
C. The notes field is too small to store all the data
D. The form does not allow for multiple users to edit notes
Q2. In the video, what does "escaping characters" mean in the context of SQL statements?
A. Deleting unnecessary spaces from a field
B. Replacing certain characters with other safe characters
C. Encrypting the data before insertion
D. Changing the data type of the field
Q3. What function does Richard recommend using to handle double quotes within the notes field?
A. Concat
B. Format
C. Replace
D. Mid
Q4. What is the purpose of the Replace function in the solution described?
A. To remove all spaces from the notes field
B. To insert double double quotes where there are double quotes
C. To add a new line after every sentence
D. To convert text to uppercase
Q5. Why do double quotes inside a text field cause a syntax error in an SQL INSERT statement?
A. Because SQL does not support text fields with quotes
B. They prematurely close the string, confusing the SQL parser
C. They delete the rest of the statement automatically
D. They are always converted to single quotes by Access
Q6. According to the video, what is a quick way to transfer current notes to the contact history table in Access?
A. Copy and paste them manually
B. Use a VBA button to automate the transfer
C. Export the data to Excel first
D. Use only action queries
Q7. In the provided VBA code solution, which variable temporarily stores the fixed notes text before insertion?
A. CustomerID
B. ArchiveBtn
C. S
D. Notes
Q8. After replacing double quotes in the notes field, what must you remember to do with the Execute SQL statement?
A. Insert the original Notes variable into the query
B. Use the modified S variable instead of Notes
C. Delete all other fields before executing
D. Compile the code before saving it
Q9. What is the benefit of archiving old notes to a contact history table as described in the video?
A. It permanently deletes outdated information
B. It keeps the active notes field smaller and provides a history of past notes
C. It prevents other users from viewing past notes
D. It allows for simultaneous editing by multiple users
Q10. Why does Richard suggest running "Debug Compile" in the VBA editor before testing the code?
A. To automatically add error handling code
B. To check for syntax errors or mistakes before running the code
C. To reset all variables used in the code
D. To execute all VBA modules at once
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is all about handling double quotes in SQL INSERT statements in Microsoft Access. This is an important topic, especially if you are archiving notes or any other text fields where users might reasonably type a double quote character.
This lesson was inspired by a question from a student using the technique from my earlier Saved Notes video. In that approach, you have a Notes field on your Customer form, and whenever you want to update those notes, you can archive the existing contents over to a contact history table, making sure you have a running record of what you and the customer have discussed. This prevents your Notes field from getting excessively large and ensures you do not lose any valuable information.
If you have not seen my Saved Notes video yet, I highly recommend you check that out first to understand the context fully. In it, I show you how to build a button right on your form. When you press this button, it picks up the contents of your Notes field and transfers them to the contact history table, then clears the Notes field so you can type something new. This makes it much easier than manually cutting and pasting every time you want to update customer notes.
For those who are new to working with VBA code in Access, I also suggest watching my beginner's tutorial on VBA programming, as well as my videos on variables and the Replace function. Understanding how single quotes, double quotes, and even double double quotes work is absolutely essential when you start interacting with SQL in VBA. These resources are all freely available on my website and YouTube channel.
Now, revisiting the scenario, imagine you have your TechHelp free template database open. You type something like "we talked about pricing," and move on with your day. After a while, those notes become outdated, and you want to archive them without losing the history. The button on your form makes this process simple.
When you create the VBA code for the button, you write an SQL statement that uses the INSERT INTO command to move the current note, along with any other necessary information like CustomerID and Description, into your contact table. You often start with hardcoded values to get the structure right, then substitute in your form's actual field values.
Here is where things get tricky. In SQL, string values are surrounded by double quotes, but what if your notes themselves contain double quotes? For example, if someone types: Rick said "there are no more jelly beans," your SQL statement will break. Access throws a syntax error because it interprets the double quotes inside your data as the end of your string, messing up the entire statement.
There are two ways to address this. One approach is to use a Recordset instead of dynamic SQL, which is more robust but also more complex and requires more code. For this lesson, though, I focus on the alternative: escaping the double quotes.
Escaping a character means replacing it with a version that your application can safely interpret. In Access SQL, this means converting each double quote inside your notes to two double quotes. This is sometimes confusing because you end up with what looks like quadruple quotes in your code as you build your strings, but it's necessary for SQL to treat the embedded quotes as literal characters.
The solution is to assign your Notes field to a temporary string variable, then use the Replace function to swap every double quote for two double quotes. For example, every instance of " becomes "". Once this replacement is done, you use the updated string in your SQL statement, and you will no longer run into those syntax errors.
It's worth emphasizing that you have to update your SQL command to refer to this new, processed variable, not the original Notes field. This is a common mistake that I see frequently, so make sure you double-check which variable you are using.
After making this adjustment, your archive function should work perfectly regardless of whether users insert double quotes in their notes. This technique is fundamental for anyone building Access applications that need to handle freeform user input.
If you enjoyed this lesson and want to improve your development skills further, I offer many more tutorials and courses, including a variety of VBA classes. You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Archiving notes from form to contact history table
Creating an archive button on a form
Writing SQL INSERT INTO statements in VBA
Inserting form field values into a table with SQL
Managing double quotes in SQL strings
Understanding the double double quote problem
Handling syntax errors from unescaped quotes
Using the Replace function to escape double quotes
Storing modified notes in a temporary variable
Updating the SQL to use the escaped variable
Blanking out the original notes field after archiving
Testing the archiving process for correct data handling |
