Wrapper Functions
By Richard Rost
45 days ago
Using Wrapper Functions to Simplify String Quotes
In this Microsoft Access tutorial, we'll talk about wrapper functions in VBA and how they can simplify your code when working with double quotes inside strings. I'll discuss the difference between using double double quotes and CHR34, and show you how to create your own wrapper function to automatically add quotes around text values. We'll walk through building and testing the function with sample data on a form, making it easier to handle quotes in your Access VBA code.
Bill from Staten Island, New York (a Platinum Member) asks: I found some people on the internet use Chr(34) & "hello" & Chr(34) instead of the double-double quotes. I tried it and it seemed easier to me. The double-double quotes still get me mixed up. I was wondering what you think about using Chr(34) instead of the double-double quotes?
Prerequisites
Recommended Courses
Keywords
TechHelp Access, wrapper functions, VBA, double double quotes, CHR34, ASCII character 34, concatenation, custom function, global function, public function, string manipulation, status function, null values, variant type, customer form, AccessLearningZone, TechHelp
Intro
In this video, we'll talk about how to create wrapper functions in Microsoft Access using VBA. I'll show you how to make a custom function that automatically wraps your text in double quotes, making it easier to handle string concatenation without getting confused by double double quotes. We'll compare using CHR34 versus double quotes, discuss common scenarios where this comes up, and walk through building and testing your own wrapper function in a global module. This solution helps streamline your code when working with string variables and quoted text in Access VBA.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today we're going to talk about wrapper functions. No, not that kind of wrapper, although that's a pretty cool picture. We'll talk about that later. We're going to talk about wrapper functions in VBA so you can take things like your double double quotes that you're sick of using, and just use a wrapper function to wrap things in double double quotes automatically for you.
Here we go.
Today's question comes from Bill in Staten Island, New York, one of my Platinum members. Bill's having problems with double double quotes and he says, I found some people on the internet use CHR34 and hello and CHR34 instead of the double double quotes. I tried it and it seems easier to me. The double double quotes still get me mixed up. I was wondering what you think about using CHR34 instead of the double double quotes.
Well, I think whatever works best for you is the best solution for you. If you think CHR34 is easier to understand, then use it. That's great.
For everybody else who's wondering what he's talking about, we've got concatenation, which is where you can put two strings together. This is basic concatenation right here. You got CHR34, which is the ASCII character for the double quotes. If you want to put them inside of quotes, then you got to use two of them. That's included in this more advanced concatenation video where we talk about double quotes and double double quotes.
If you got a phrase like this and you want to put it into a string, you have to do it like this with two double quotes, which I call double double quotes. I made them in red here so you could see that two of those double quotes becomes a double quote inside a string.
Now, this gets even more complicated if this is a person's name or maybe not a name, or you change the greeting, whatever she said. If that hello can be anything - it could be hi, bonjour, or whatever - then you want to have it in another variable called greeting. Now you got to put the double double quotes in there to give you that quote. That closes the string and then you got an ampersand and then the variable here or the field name and then you got to open the string back up, the period, the double quotes inside the string, and then close the string again.
Now what Bill is saying you could do is use CHR34 instead, which is the same thing as the quote inside the string. It is basically the ASCII version of the double quote character. Every letter has its own ASCII code, which is a number, and you can represent that with the CHR function that takes character 34 and puts it in that spot.
If you think that this is easier, wherever you want a quote, put a CHR34. That works great. You can do that. That's perfectly fine, perfectly valid.
You could also use your own wrapper function. If you use this a lot, I don't mean wrapper like M&M rapper, I mean like a wrapper - you wrap it in something so you can make it so that if you know you use these quotes a lot in your code, you could just make your own wrapper that says, take whatever I give you and put that inside of quotes and add it to the string. And that's what I'm going to show you how to do right now.
Now this will involve a little VBA. If you've never done any VBA programming before, go watch this video first, about 20 minutes long. It'll teach you everything you need to know to get started. Then go watch this video where I teach you how to create your own custom function, because we're going to make a function that's going to take whatever text we send it and wrap it inside of quotes. These are all free videos. They're on my website. They're on my YouTube channel. Go watch them and come on back.
All right, so here I am in my TechHelp free template. This is a free database. You can grab it up on my website if you want to. I'm going to create a global function, so I'm going to come down here in my global module.
This is where I'm going to put all the stuff that I want my whole database to be able to use. I got a couple of things in here like a sleep timer and a status function. I got separate videos on those if you want. Go search my website for them. You'll find them.
Down here we're just going to say I want to create a public function. Public means the whole database can use it. A function means it's going to return a value as opposed to a subroutine, which is another type of procedure that just does stuff. It doesn't return a value.
So my public function, what am I going to call it? Let's just call it wrapper. You can name it whatever you want. Wrap it in quotes or whatever.
We're going to send an S as a string, and we're going to return a string to whoever called it. So as a string here.
Now inside the wrapper function, all we're going to do, very simple, is we're going to say wrapper equals. Now we're going to wrap it inside double double quotes. You can put it like this if you want. That's a set of double quotes inside a string. Or you could put the CHR34 there if you prefer, whatever you want. I like the double double quotes myself.
Hold on. Someone's beaming in. It's just one of the Klingons I had over from my Thanksgiving dinner that went Black Friday shopping. If you want to know what I'm talking about, go check my Captain's Log.
So in here, we're going to do that. I'm going to put S, and then we're going to put quote quote quote quote again. So it's another set of quotes inside of a set of quotes. Anything that I send in a wrapper, which is going to send it back like that.
We can test this very easily. I'm going to use my status box function over here. So I'm going to go into this Hello World button. All the status function does is it displays stuff in this box here.
So build event on that. I'm going to say again, status Hello World. But this time, I'm going to put Hello World inside of my wrapper function. So wrapper, and then we're going to put that inside of quotes just like that. So it should return Hello World actually in quotes.
Save it. Yes. Give it a debug compile once in a while.
Let's close the main menu and rerun. Now I'm going to hit the button. Boom. Look at that. I got Hello World, but it's inside of quotes. So now whatever I put in there is inside the wrapper function.
Now let's say you want to do something on the customer form. Let's say maybe the first and last name.
Design view. I'm going to just copy and paste this button. Copy, paste. We'll just put an X in here for the caption. Right click, build event.
Normally I'd name my buttons, but I'm being lazy right now. Here I could say status, because it's a global function. Status will say first name and a space and last name.
Let's see what that gives us. Let me just slide this over here. We're going to close this. We're going to reopen the customer form. Now I got to move it over here so we can still see the status box. If I click on that button now, I get just Richard Rost.
Now, if I put that inside the wrapper function like this, if I click the button now, look at that. Richard Rost is inside of quotes.
Now you could say stuff like you can come in here and say, she said, and then you want to wrap the whole thing in here. You put the thing in here, you'd say, well, I'm in the wrong place with my she said, so we're going to move the she said outside. So she said, and then in the wrapper, you put what she said. You put in here, maybe hello, comma and first name, last name, and a period like that. Now it's going to be, she said, and then inside of quotes because of the wrapper, hello, Richard Rost.
Save it. Then you hit the button. Boom. There you go. The wrapper function takes care of that part, putting what you send to it inside of quotes.
Now it makes it easier because you don't have to worry about putting double double quotes inside the strings. You just put it inside of wrapper, and whatever goes inside the wrapper function gets quotes around it, and that's handled by this guy. It's that simple.
It's little things like that that make your coding a whole lot more pleasurable. Just be careful with null values. You might need to then switch this to a variant and check to see if it's null, but that's a whole different video. I got a whole video on variants if you're curious about that.
There you go. There's your basic wrapper functions. If you have any questions, comments, thoughts, concerns, post them down below in the comments section.
That's going to pretty much do it for your TechHelp video for today. I just want to take a second to acknowledge the cool picture that Gemini made for me. I said up front, when I made this video, I'm like, I'm going to do an image that's like a play on the word wrapper. Instead of wrapper with a W, I want wrapper with an R.
So I told Gemini, I gave it a brief outline of what the video is about. I said make me a picture with me as a wrapper on stage. I want an Access logo on the hat, and make it nerdy. It added the keyboard tie, which I love because I'm a keyboard player. It must have gleaned that from previous conversations. It's got the floppy disk and a circuit board there. It's got the pocket protector and a wrapper function. That looks like C or something C++ on the background, but okay, close enough. That's so awesome.
I asked ChatGPT for one first and it gave me that, which isn't bad. That's okay. It's kind of close to my face, but not really. But Gemini's was just so much better. So right now Gemini is winning on the AI image generation front.
So that's just cool.
As I was saying, there's your TechHelp video for today. Hope you learned something.
Live long and prosper, my friends. I'll see you next time.
Quiz
Q1. What is the primary purpose of a wrapper function as described in the video?
A. To wrap a given string in double quotes automatically
B. To convert numbers to strings
C. To display error messages in VBA
D. To split a string into an array
Q2. What is CHR34 in VBA?
A. The ASCII code for a space character
B. The ASCII code for a double quote character
C. The function for removing quotes
D. The ASCII code for a comma
Q3. Why might someone prefer using CHR34 over double double quotes in VBA string concatenation?
A. It improves code performance significantly
B. It eliminates the need for concatenation
C. It can be easier to read and understand than using multiple double quotes
D. It is required by the VBA compiler
Q4. How does the wrapper function help when dealing with variables that need to be wrapped in quotes?
A. It hides the variable from the user
B. It automatically adds double quotes around any input, reducing the chance for errors
C. It replaces double quotes with single quotes
D. It prevents the code from executing
Q5. What is the correct way to declare a public function in a global module so the entire database can use it?
A. Private Sub MyFunction
B. Public Module MyFunction
C. Public Function MyFunction(parameter As String) As String
D. Sub PublicFunction(parameter As String)
Q6. In the wrapper function example given in the video, what is returned by the function?
A. The ASCII code for the input
B. The input string unchanged
C. The input string surrounded by double quotes
D. The input string in all uppercase
Q7. What is the potential issue to be aware of when passing null values to the wrapper function?
A. It may crash the computer
B. It may cause a type mismatch or require special handling such as using a variant
C. It will double the input value
D. It will remove all spaces from the string
Q8. According to the video, when should you use double double quotes inside a string in VBA?
A. When you want to represent a double quote inside a string
B. When you want to end a line in VBA
C. When you want to concatenate two numbers
D. When you want to comment out a line
Q9. What type of procedure is a function in VBA, according to the video's explanation?
A. One that returns a value to the caller
B. One that only displays a message
C. One that only runs a macro
D. One that closes forms
Q10. Which of the following is NOT mentioned as a valid method for including double quotes in VBA strings?
A. Using double double quotes
B. Using CHR34
C. Using a wrapper function
D. Using single quotes directly
Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-C; 7-B; 8-A; 9-A; 10-D
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 wrapper functions in VBA for Microsoft Access. I want to address a common issue that many Access developers face when building string expressions in VBA: dealing with double double quotes in your code. It can quickly get confusing, especially if you're frequently building dynamic strings for things like SQL statements.
This topic came up thanks to a question from one of our viewers who finds the double double quotes hard to work with. Instead, he saw people online using CHR34 for the double quote character and wondered if that's a better solution.
To start, let me say that whatever method works best for you is the one you should use. If CHR34 helps make your code clearer and easier for you to read, then, by all means, go with that. For those not familiar, CHR34 is the ASCII code for a double quote, so whenever you call CHR34 in your code, it inserts a quote character. This approach can be much easier than trying to remember where all your double quotes go, especially when they're nested.
Let's break down the issue a bit further. Suppose you want to put a string value inside double quotes in VBA. You could write it directly with double double quotes. For example, when you want to create the output "Hello," you'd end up with something like ""Hello"" in your VBA code. It gets even trickier if you are concatenating variables, for example, if you have a greeting variable that could contain any text.
Managing the placement of these quotes often results in complex, hard-to-read code. That's where the CHR34 technique can come in, allowing you to replace the literal double quotes with something clearer. For example, you can break up your string by inserting CHR34 where the quotes should appear.
If you find yourself needing to add quotes around text frequently in your code, you might want to consider creating your own wrapper function. The concept is simple: you write a function that accepts a string and returns that string wrapped in quotes. So instead of adding double double quotes or CHR34 every time, you just call your wrapper function and pass it the text you want in quotes. This not only makes your code cleaner but also saves time and effort.
To create this wrapper function, you'll need to do a little VBA programming. If VBA is new to you, I suggest you check out my introductory videos that cover the basics of VBA programming in Access and how to create your own custom functions.
In my demonstration, I show how to put this wrapper function into a global module so that it's accessible throughout your entire database. It's as simple as defining a public function that takes a string input and returns it with quotes around it. You can choose whether to use double double quotes or CHR34 inside your function. I personally prefer the double double quotes method, but both work just fine.
To test this function, you can use it in various forms and controls throughout your database. For example, you might have a button that displays a message in a status box. Instead of laboriously adding the quotes in your string each time, just wrap your text in your new wrapper function, and it will handle adding the quotes for you.
If you're working on forms and want to display customer names with quotes around them, you can easily do so by combining the fields and then simply calling the wrapper function with the result. This approach keeps your code easy to read and eliminates mistakes with misplaced quotes.
It's also helpful when you want to add extra text to your output. For example, you might want to display something like: She said, "Hello, John Smith." With the wrapper function, you build your string as usual, and then just pass the dynamic part to the wrapper so that it's always quoted correctly.
One thing to keep in mind as you work with wrapper functions is the handling of null values. If you send a null value to the function, you might run into trouble. If that's a concern, consider making your wrapper function accept a variant and include a check for null. I have a separate video that goes into handling variants and working safely with nulls.
This technique of using a wrapper function is a great example of how a small change can make your code much neater and more enjoyable to work with. It eliminates a lot of the hassle and mental gymnastics of getting all your quote marks in the right places.
If you have any questions or suggestions about wrapper functions, or tips for making your code cleaner in Access VBA, I'd love to hear from you in the comments.
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 List
Wrapper functions in VBA
Concatenating strings with double quotes in VBA
Using CHR34 to insert double quotes in strings
Creating a public global function in VBA
Defining a custom wrapper function to add quotes
Returning string values from custom functions
Testing a wrapper function with a status display
Applying the wrapper function to form fields
Combining variables inside quoted strings with the wrapper function
Article
Today I want to introduce you to a useful programming technique in VBA called a wrapper function, specifically for handling double quotes in your code. If you have ever found yourself getting tripped up by having to use lots of double double quotes in your VBA strings, or if you have tried using CHR(34) and are looking for a smoother way to deal with string quoting, this method will make your life easier.
When working in VBA, you often need to concatenate strings, and sometimes you want your text to include double quotes. For example, if you want your output to be "hello", you have to use two double quote characters inside a string, like this: ""hello"". It gets more complicated when variables are involved. Suppose you have a variable called greeting, and you want to display "hello", "hi", or any greeting that is assigned to that variable. In string concatenation, you would have to carefully place those double double quotes and ampersands to build the string correctly, and that can be error-prone and a little confusing.
Some developers prefer to use the CHR(34) function, which returns the ASCII character for a double quote. For example, you can write CHR(34) & greeting & CHR(34). This does exactly the same thing and is perfectly valid. It's really up to you which method is easier to understand when you come back to your code later.
However, if you find yourself handling this sort of thing frequently, it makes sense to create your own wrapper function. In programming, a wrapper is just a function you write that takes some input and "wraps" it in something else, in this case, double quotes.
Let me show you how you can create such a function in VBA. Open your VBA editor and find or create a module you want accessible throughout your database. Then, add the following code:
Public Function wrapper(s As String) As String
wrapper = """" & s & """"
End Function
Let's break that down. The function is called wrapper, and it takes one input, s, which should be a string. Inside the function, the value of s is placed between two double quotes. The way the code is written, """" produces a single double quote character, and at the end, you'll see another """" which does the same. The result is that whatever string you pass to wrapper comes back surrounded by quotes.
If you prefer, you could use CHR(34) instead, like this:
Public Function wrapper(s As String) As String
wrapper = Chr(34) & s & Chr(34)
End Function
Now, whenever you need to wrap something in quotes in your VBA code, you can just call wrapper with your text or variable. For example, if you want to show a status message that says "Hello World", you could write:
Status wrapper("Hello World")
The Status procedure would show you Hello World, in quotes.
You can also use this function to display more complex phrases. For example, suppose you have a customer form, and you want to display a person's first and last name in quotes, you can use:
Status wrapper([FirstName] & " " & [LastName])
If you want to do something like show a reporting string that says: She said "hello, Richard Rost." you can construct it as:
Status "She said " & wrapper("hello, " & [FirstName] & " " & [LastName] & ".")
Here, wrapper takes care of putting the quotes around the greeting and name, so you do not have to wrestle with messy double double quotes.
This makes your code easier to read, maintain, and saves you from the hassle of getting lost in nested quotes. You just send in whatever you want quoted, and the wrapper function returns it, neatly enclosed.
Note that this basic wrapper function assumes you are always passing valid strings. If you expect Null values or need to handle different data types, you might want to modify the function to accept a Variant and check for Nulls, but for most simple uses, the version shown here will be fine.
Building wrapper functions like this is a great practice. Whenever you spot a task you do repeatedly in your code, it usually means you can create a reusable function to simplify your life. This approach not only makes coding easier and less error-prone, but it also makes your intent clear when you read your code again in the future.
If you have any questions about this or run into any issues, feel free to ask. Using wrapper functions is a simple step, but it makes working with VBA strings a lot more pleasant. Give it a try and see how much neater your code can look.
|
