|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Double Double Quotes By Richard Rost 
6 years ago
Learn About Concatenation & the Double-Double Quote Problem In this Microsoft Access TechHelp video, I'll show you how to use string concatenation to join two fields together in a query, and in a VBA text string. Using quotes, single quotes, double quotes, double-double quotes, can be confusing. We'll try to sort it all out. MembersThere is no Extended Cut for this video. Links
IntroIn this video, we will talk about how to properly concatenate strings and manage quotes in Microsoft Access queries and VBA. I will explain when to use single quotes, double quotes, and the tricky double double quotes, especially when building expressions that include fields and literal text. We will look at practical examples such as combining first and last names, adding literal characters, inserting quotes within strings, and working with variables in SQL statements and VBA. I will also discuss common pitfalls and share some helpful techniques to make string concatenation in Microsoft Access less confusing.TranscriptWelcome to another TechHelp with Richard Rost, brought to you by AccessLearningZone.com.In today's lesson, we are going to talk about string concatenation. I have gotten a lot of emails about this over the years, and one in particular in our Facebook group today by Brent. It is about string concatenation and putting fields together, putting strings together in VBA, when to use single quotes, when to use double quotes, and so on. I talk about this in a lot of my different lessons. I will put links in the description below. It is something that comes up quite a lot in my classes. Students ask me about it all the time, and it can be confusing until you get the hang of it. Here is Brent's question: He says, "I am sure this is a simple question. I hope there are others that are with me, and maybe you have discussed it in your lessons in great detail, and I just am not grasping it. I seem to struggle with all this concatenation and getting the right amount of quotation marks. When I am tailoring something I learned in class, but it is not exactly the same, I run into problems because I need to remove info and somewhere I am not putting in or removing enough quotes or placing the ampersand in the right place. I have been with these classes for years now, and I still cannot grasp this concept. Is there a tutorial, or can you make a short detailed tutorial really explaining these quotes and how to work with them? I am probably missing something very simple to turn that light bulb on. Tonight, I was going back over Developer 3, Lesson 4, and where the DoCmd.RunSQL statement is, it is really throwing me for a loop. Any help would be lifesaving." Well, do not feel bad, Brent, you are not alone. I get lots and lots of people that ask me the same question. So let's take a minute and run through the basic concatenation, and I will throw in some tricks and some techniques. Maybe watching it again in a different context will turn that light bulb on. First things first, go and watch this Concatenation tip. It is on my website. It is pretty old, from 2008, but all the concepts are still perfectly valid. There is the link for it, and I will put the link in the description below the video so you can click on it. Rewatch that before continuing with this video. I will recover some of the concepts, basically and briefly, but that gives you the basic beginner information on how to concatenate two fields together. There are a couple of different ways to concatenate. First thing is, if you want to put fields together from a table like first name and last name, we are going to use a query to do that. Real simple query; here is first name, here is last name. We will create a query: Create Query Design, bring in the customer table. If I put first name and last name down here in the query by themselves, they show up as separate fields. If you want to put them together in another field, you use string concatenation. It looks like this: FullName (that is the name of the field you want to create), colon, FirstName ampersand LastName. That ampersand puts together two fields or two other bits of information. If I run this now, you can see they are together but there is no space between them. If you want to add literal characters in here, you have to put the string of text inside of quotes. A simpler example: instead of putting these two together, let's say you want to say "Mr." and then first name. So out in front of first name, inside of quotes: "Mr. " (with a period and space, in quotes). That is a string of characters, M R period space, that I want to put in front of first name. How do I put them together? Again, the ampersand. That is the concatenation operator. Run it now. I have "Mr. Joe", "Mr. Sue", "Ms. Bill", and of course, you would want to use an If function or something to look and see if it is male or female or whatever you want to put there, but the point here is the concatenation. Now, instead of putting "Mr." in front of first name, I want to make it "first name space last name." So after first name, ampersand, what am I putting in here? Quote, space, quote. Those are double quotes. Ampersand last name. That is how you put first name and last name together. If you want to go last name, first name, you can do the same trick. "LastFirst" is the name of the field we are creating: last name ampersand quote, comma, space, quote. That is an actual string of characters, and first name. Just like that. Run it and there you go. Yeah, it gets more complicated if they have multiple names in the field and all that stuff, but these are the basics. Now, the double double quotes and the single quotes come in - well, we are going to forget single quotes for now. Save single quotes for when you are working with SQL, which I will talk about in a minute. For now, let's talk about the double double quotes. Let's get rid of this stuff here real quick. Delete and delete. Let's say I want to create a field in here called "Greeting," and the greeting is going to be inside of quotes: She said, "Hello Brent." That is the greeting. Now, every field should get the same bit of text. There you go: "She said, 'Hello Brent.'" If I want to replace Brent with the actual first name of the character, get rid of it inside of here, and then concatenate it on the end: first name, like that. "She said, 'Hello Sue.'", "She said, 'Hello Joe.'", and so on. Now, what if you want to make this perfect, so let's go back to "Hello Brent." What if you want to make this perfect grammar, where there are actually quotes inside of the greeting itself? If you want to have quotes inside of an actual string, you have to use double quotes. I want this to display: She said, quote, Hello Brent. So that double quote is going to turn into a quote inside of there. Let's take a look at what we have: She said, then the quotes are now displayed in the results, "Hello Brent," and of course we need one on the end. Now, this is where it gets a little confusing. You have to put a double double quote inside there like that and pop it up in Notepad - it is easier to see. I can zoom in the video, but it is easier to use Notepad. Greeting: She said "Hello Brent." See how these turn into quotes inside of a string of text? That is when you need the double double quotes to put an actual quote character there. Now it is going to get even more complicated because now we want to replace Brent with the actual FirstName field. So now we have to end the string here and then begin it again over there. This is how it goes: close the string there, and FirstName, and open the string back up again, and just get rid of Brent. See? That is what it looks like. And then run. See: "She said 'Hello Joe.'" Let me put this in Notepad so you can get a good look at it. There it is in Notepad. I know this all looks crazy, but this is how it has to go. The evolution is: "She said 'Hello Brent.'" The first thing you have to do is treat this whole thing like a string, so that all has to go inside of quotes. Then, you have to replace these guys with double quotes. Then, you have to get rid of this and put the field name in there. It is just substitution. Do it one step at a time if it is confusing for you. Let's see how this works in VBA. It is very similar. I am not going to save that query. Let's make a little form: Create Form Design. Let's base this on my customer table. Let's bring in some existing fields: we just need FirstName and LastName. Resize this a little bit. Give it a splash of color. I can't deal with these blank white forms. Let's throw a command button on, and a basic text box down here for our results. Get rid of that. I'll call this "myResults." If you don't know what I am doing here, you have to watch my beginner VBA lessons. I am just throwing down a basic text box to get some stuff. What stuff is going to be determined by this command button that I click. Cancel the wizard. I am going to just put the word "Go" in here; that is going to do our stuff. What stuff is it going to do? Well, let's right-click on it and build the event code builder. Here is my VB window. With my resolution so high now, I can work on this side by side for you. I like this. In my older videos, I used to have a much smaller window. I have just started working with high def a little while ago, tried to keep the video size as small, but everyone's got high speed internet nowadays, so it does not really matter. So what is the button going to do? Results equals FirstName and a space and LastName. Just like that. Save it, save the form as "Customer." Let's switch back to form view. There we go. See, I click on the button, and it generates this code: Results equals FirstName and a space and LastName. There we go. If you want to switch it around, same thing: LastName and quote, comma, quote, and FirstName. Then Go. Same. There is our VBA. Want to do the "She said" result thing again? Let's do "She said, Hello Brent." Go. All I get is "She said, Hello Brent." Let's replace Brent with FirstName. Do it in steps if you want to. Get rid of that, Hello and FirstName. So we replaced the actual word Brent with the FirstName field. See: "She said, Hello Joe." If you change to a different record: "She said, Hello Sue." Now let's put in the actual quote characters. The opening quote is going to go right here, right in front of Hello. It has to be a double double quote, like that. There is my opening quotation. It is inside the string value. We have to add some stuff to the end of this string to put the closing quote in there. After FirstName, tack on some more stuff. What is going to be the stuff here? If I just put like that, I am going to get actual x's. That is some stuff to go at the end; see that? But I do not want x's there. I want a period and another quote. So let's just start with the period. There is the period. Now after that period, I am going to stick in another double quote, which is going to go like that. You just have to remember these guys replace the double quote inside of a string value. You can use single quotes, too, if you want. You can use a single quote like that, and this one here would replace that one if that is what you want. That is okay, then go ahead. This comes into play, and this is easier to conceptualize when you are working with SQL because you can get away with these in SQL, but if you want true English, true wording, this will come in handy if you are writing letters and you want to put this kind of stuff inside of your reports for printing out. You can also build these things. If you are not comfortable doing this all in one line like this, you can build these in pieces. You can go like: She said or She said Now you get that. Now you just tack on the results, right? Results equals results and you want to put your quote in there like that. Oops, Results. That is one thing I love about Access VBA, it will capitalize for you. See, now I get the extra quote in there. Results equals results and put the quotation mark in there. Now, what is next? Results equals results and " Hello " like that. Do it again. Results equals results and FirstName. Then we are going to close it up with: Results equals results and period, close quotes like that. That is a whole bunch of concatenation for you. You can build the same field if you want to, or you can do it in a memory variable. You can dim yourself an S up top here: Dim S as String. Maybe there is all stuff I cover in my basic VBA classes. Then you can make this S, you can manipulate S all the way down, and at the very end just say: Results equals S. Same results. Let's try something with a basic SQL statement. I have an SQL basics tutorial on YouTube. I will put a link to it in the description below the video. Watch that if you haven't done any SQL. You can use SQL statements to manipulate the data in a form or a report, whatever. Let's say we want to programmatically change the data in here. I already ran one; you can see it is down to record 1 of 1. Here is an example: S equals "SELECT * FROM CustomerT". Then I am going to say Me.RecordSource equals S. And just to put something in the box, Results equals "Done". What this does is now, you see "1 of 3" records down here. I said, "SELECT * FROM CustomerT". If you scroll through there, you can see them all. If I want to add some kind of a limitation on here, a criteria, I can say: SELECT * FROM CustomerT WHERE FirstName = Joe like this. Run it, and now I am getting a parameter value. Why is that? Because Access thinks that Joe is a field. So what we have to do is we have to put Joe in here as a text string, which requires - guess what - these guys. I do not want FirstName equaling a field name Joe, I want FirstName equaling the actual text Joe. Now when I run it, you can see this is 1 of 1 down here. I am only seeing that one record. Now, here is where the single quotes come in. You can actually get away with doing this, because with programming, sometimes those double double quotes get a little confusing. Click, and then the same results come in here. With SQL, feel free to use the single quotes. I do not, because some other versions of SQL do not allow this and some do, so it is hard to keep track of which is which. I personally like to use the double double quotes. Now, this is again where it becomes a little crazy. If you want to use a variable in here, let's say you are already using this field here, let's make another variable: Dim fn as String Here I am going to say: fn = "Joe". So I have made a variable and set the value equal to fn. Now I want to replace Joe with fn. Well, I cannot just put fn in there, because it is not going to know what it is. If I run it now, I get nothing. There is no record in here "fn". If I get rid of the double double quotes like I had before, I still get a parameter value because fn is not a field in the table, so that does not work either. It is a variable I created. So what you have to do is say: WHERE FirstName = " & fn & " You have to say: WHERE FirstName = " + double double quote, close the string, and fn, and double double quote, open the string again, like that. What happens is this becomes the opening quote, that closes this string, this is another string, and inside that string is just fn. Now you get that, and now it works. If it helps you to conceptualize this, replace these double quotes with single quotes like that, and those single quotes are now what are used in the parameter for the actual string here. I know it is a little confusing, and really the best way to get around this is just to keep doing it. It took me a while to wrap my head around this, too. I have been doing this for 20 plus years, and this is my full-time job. This is what I do all the time is teach Access, so it is still a little confusing to me sometimes. Do not feel bad. Just remember one more thing: if you are using dates, you have to use the pound symbols. I think we have a CustomerSince field in here. Let's see. There is the one downside to shrinking everything. Let's see here, I do have a CustomerSince field, 2020 and 1999. If I do less than 2000, I will get just Bill Williams. That is what I wanted to see. So, if you want to say show all records where CustomerSince is less than, now you need the pound signs, and then inside the pound signs you can go 1/1/2000 like that, and then run it and there is just Bill Williams. He is the only one in here from less than 2020. Now, if you want to make this date value a parameter, let's say fn is now a date, and the date is going to be 1/1/2000. You can come over here and if you close this up and say fn, now that is my date. We run it, we still get no values. Why? Because we have to put those pound signs in the SQL as well, like that, and now there is our Bill Williams back. Even though you have delimited them up here, by setting the date in the variable, you also have to have those in here as well, and that is part of concatenation. I hope this helped. It is just a review of a lot of different stuff that I cover in multiple different classes. I put links to all of them in the description field below the video. Usually, I do screenshots of them here, but I am not going to make screenshots for all of them. Access Beginner 9 is where I cover the basics of putting strings together in queries. Access Expert 8 and Expert 25 cover more, and then in Access Developer 2 is where I start doing it with VBA. If you want to get your question answered, send it to me here on the TechHelp page. I do read through all of them. I do not always have time to make little videos like this, but sometimes I will get back to you with a quick email if I can. We are trying to promote a new Facebook group. If you are on Facebook, join the Access Learning Zone Discussion Group. There is a link to it. We are up to 232 members, and we just started this a couple weeks ago, so it is getting there. That is where I found Brent's question for today's video; he posted it in the Access Learning Zone Facebook group. I know a lot of you are not on Facebook, so we are looking for another solution as well. There are forums on my website, but I do not know if I am going to keep them or not because I am trying to get something with more features, and I do not really want to build all those features myself because I would rather make more Access videos than work on my website. So I am looking at a couple of different solutions, but so far the group is working pretty well. There is me, there is my email if you want to drop me a quick email. I cannot promise a personal response to everybody. I do get a lot of email, but if your email does catch my eye, I might make a video out of it. There are all my other things: blog, Facebook, Twitter, YouTube. Time for shameless self-promotion. Access Level 1 is free. There is a link, or you can find it in the description below. Three hours on YouTube, watch it, all the basics. If you like it, Level 2 is just one dollar. If you want to keep learning beyond that, I do have a membership option available called the Learning Connection, where you get one new class per week at a 50 percent discount, so that is pretty cool too. There is a link for that. Make sure you subscribe to my channel. Click the subscribe button and click that little bell. Make sure you subscribe to all so you get notifications in your email and such when I produce new videos and post them up here on YouTube. Thanks for watching, and we will see you next time. Thanks for learning with AccessLearningZone.com. QuizQ1. What operator is used in Access and VBA to concatenate (join) two strings together?A. Plus (+) B. Ampersand (&) C. Asterisk (*) D. Pipe (|) Q2. If you want to add a literal space between two concatenated fields in a query, which of the following is correct? A. FirstName & LastName B. FirstName & ' ' & LastName C. FirstName & " " & LastName D. FirstName " " LastName Q3. In an Access query, how would you display the last name, then a comma and space, then the first name? A. LastName, FirstName B. LastName & ", " & FirstName C. LastName + ', ' + FirstName D. LastName & FirstName, ", " Q4. When inserting quote characters inside a string in Access or VBA, which technique should you use? A. Use single quotes directly B. Escape the quote with a backslash C. Use double double quotes ("") D. Only use semicolons Q5. What is the main reason double double quotes ("") are needed within string values? A. To indicate the end of a string B. To add a space between words C. To include an actual quote character inside the string D. To abbreviate long variable names Q6. When should single quotes be primarily used in Access SQL statements? A. To join numbers B. When dealing with field names C. To delimit string values in SQL criteria D. Whenever you see double quotes Q7. In an SQL statement built in VBA, how do you correctly insert the value of a string variable (fn) into a WHERE clause comparing against a text field? A. WHERE FirstName = fn B. WHERE FirstName = "" & fn & "" C. WHERE FirstName = 'fn' D. WHERE FirstName = [fn] Q8. When building SQL criteria for date values in Access, which delimiters must you use around the date? A. Single quotes (') B. Parentheses ( ) C. Double quotes (") D. Pound signs (#) Q9. What happens if you forget to include quote marks around a string value in an Access SQL WHERE clause? A. The query always works anyway B. Access thinks it is a field name and prompts for a parameter value C. The result is always zero records D. Access crashes Q10. If you are finding string concatenation with quotation marks confusing, what did Richard recommend as a way to handle it? A. Always hard-code all values B. Try a different database system C. Build your concatenated strings one piece at a time, step by step D. Never use VBA for strings Q11. In Access VBA, if you want to assign the result of a concatenation to a results text box, which is the correct syntax? A. results = FirstName & LastName B. Results = FirstName & " " & LastName C. Results = FirstName " " LastName D. results: FirstName & LastName Q12. If you want to use a variable for a date value in a query, what else must you place in the SQL string? A. Periods (.) B. Parentheses C. Pound signs (#) surrounding the variable D. Nothing extra Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-D; 9-B; 10-C; 11-B; 12-C 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. SummaryToday's video from Access Learning Zone covers the topic of string concatenation in Microsoft Access. This is a question I receive frequently from students, and one that came up in our Facebook group recently, thanks to Brent. Concatenation, or joining text strings together, often trips up even experienced users, particularly when dealing with quotes and ampersands in queries and in VBA. Many people find the correct use of single quotes, double quotes, and ampersands confusing, especially when adapting examples from previous lessons to new scenarios. My goal here is to break it down clearly and provide practical techniques to help make sense of it all.When you want to combine fields such as first name and last name in a query, Access requires you to use the ampersand as the concatenation operator. If you simply add both fields, they appear separately. However, if you create a new field in your query—for example, FullName—you can define it as FirstName ampersand LastName to join them together. Of course, without adding anything else, there will be no space between the two names. To add a space, insert a string containing just a space inside double quotes between the fields. For instance, FullName: FirstName ampersand " " ampersand LastName produces the result most people are looking for. You can use this same logic to add other characters, such as commas or titles, by placing them in quotes and joining them wherever needed in your expression. Occasionally, you want to include literal quotes inside your strings. This is where double double quotes come into play. If you want the result to show something like She said, "Hello Brent," you have to use two double quotes together within the string each time you want an actual quotation mark to appear in your output. This process can look a little odd at first, but it's essential for getting those quote marks to appear correctly. Suppose you want to customize the greeting to include the person's name from your data. Instead of hard-coding a name, you concatenate the field value into the string at the appropriate spot, breaking and restarting your string with ampersands as needed. For clarity, it's often a good approach to build the string step by step, testing as you go. The same rules apply when you do this in VBA. Start by placing a text box and a command button on your form. Use VBA to set the text box value to your concatenated result when the button is clicked. You can build your concatenated string in one step or in several, assigning portions of the string to your variable and updating it by adding new pieces. This modular approach often makes your code easier to read and troubleshoot. When building SQL statements in VBA, especially those involving criteria, recognizing when to use single quotes versus double quotes is important. For string values in SQL, you need to enclose those values in quotes within your SQL expression. Double quotes are generally safest in Access, especially since not all SQL implementations handle single quotes the same way. When you want to insert a variable into your SQL command, you must close your existing quoted string, insert the variable with ampersands, and then start a new quoted string for the remainder. Working with dates introduces another level of complexity. Access SQL requires dates to be surrounded by pound signs. You must ensure these are included as you build your string—both for literal dates and variables containing date values. As with a lot of topics in Access, this process becomes more intuitive with practice. If you find it confusing at first, try working through your string-building one piece at a time. Substitution and testing at each stage help you identify exactly where the quotes and ampersands belong. I've listed several video lessons on my website that cover these concepts in both queries and VBA, ranging from introductory to more advanced topics. If you have questions, I encourage you to participate in our Access Learning Zone Discussion Group on Facebook, where community members often help with these kinds of challenges. You can also submit questions through the TechHelp page on my website—while I can't answer everyone personally, I do try to create videos for some of the more common issues. Finally, remember that Access Level 1 is available for free on YouTube, and Level 2 is just a dollar. If you'd like to continue learning, consider joining the Learning Connection for regular discounted lessons. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListString concatenation in Access queriesUsing ampersand to join fields in queries Adding literal text in concatenation Inserting spaces and punctuation in concatenated strings Inserting quotes inside concatenated strings Using double double quotes for embedded quotes Concatenating fields with variables in queries Building concatenated strings in VBA Updating text box values using VBA concatenation Swapping field order in concatenated strings Building strings step by step in VBA Creating SQL statements through concatenation in VBA Using string variables for dynamic SQL Handling quotes in SQL string concatenation Using single quotes versus double quotes in SQL Embedding variables in SQL statement strings Using pound signs for date values in SQL Concatenating date values as parameters in SQL Common mistakes with string concatenation and solutions Practical VBA examples for string concatenation |
||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access Concatenation, Double Double Quotes, Single Quotes, TechHelp ddq PermaLink Double Double Quotes in Microsoft Access |
