InStr Function
By Richard Rost
4 years ago
Find a String Within a String in Microsoft Access
In this Microsoft Access tutorial, I'm going to show you how to determine whether or not a specific substring appears inside of another string using the InStr function in a query. It also works in VBA.
Freddy from Arlington, Texas (a Gold Member) asks: My vendor uses a status code that has the characters "DSC" in it if the part has been discontinued. Problem is, that status code also includes a bunch of other random information we don't care about. How can I indicate which parts have that code. I know how to do a search manually, and I know how to do a wildcard search in a query, but that shows ONLY those records. I still want to see all of the rest of them.
Pre-Requisites
Links
Recommended Courses
 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, #fasttips, InStr Function, string within a string, find a character in a string, search for a word, find a substring
Subscribe to InStr Function
Get notifications when this page is updated
Intro
In this video, we will talk about how to use the InStr function in Microsoft Access to find a specific string within another string, such as identifying discontinued products with a "DSC" code in a status field. We will see how to create calculated query fields, use the IIf function for true/false results, and format query output to display "True/False" or "Yes/No" values. This video also covers handling nulls, tips for building readable queries, and best practices for structuring your logic when searching for specific text within fields.
Transcript
Welcome to another fast tip video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to use the InStr function to find a string within a string in Microsoft Access.
Today's question comes from Freddie from Arlington, Texas, one of my Gold Members. Freddie says, my vendor uses a status code that has the characters DSC in it. The part has been discontinued. Problem is, that status code also includes a bunch of other random information that we do not care about. How can I indicate which parts have that code? I know how to do a search manually, and I know how to do a wildcard search in a query, but that shows only those records. I still want to see all the rest of them.
Well, Freddie, in this case we're going to learn how to use the InStr function, which will tell you if a string - in this case, "DSC" - appears inside of another string, which will be your status code.
Before we get started, if you do not know how to make calculated query fields, go watch this video. We're going to need to use the IIf function, the immediate if function, which is basically an if-then statement inside of a query, but watch that if you do not know how to use it. And optionally, if you do not know how to do a wildcard search in a query, go watch this video. You can go through on my website, through on my YouTube channel, go watch them and come back.
Here I am in my TechHelp free template. This is a free database. You can go grab a copy from my website if you want to.
Let's pretend that our CustomerT here - let's pretend the Last Name field, this one here - let's pretend the Last Name is Freddie's vendor status code, and some of them have "DSC" in them to indicate they're discontinued, so we will put a couple "DSC"s in here somewhere.
Freddie says the code can have other random information in it. I understand what you are talking about. I had a customer that had to deal with something like this a few years back, and just randomly, some products are discontinued and the status code might have other things in there like "harmony" or "in stock" - all in one status code. People do weird things in their databases, and we just have to deal with them.
I have got a bunch of Last Names here that, for some of them, have "DSC" and some of them do not. For some of them, it is in the first part of the field here. Let's put one in the first character, right there in front of Jane Wake.
Now, what Freddie is talking about is you could easily do a query using a wildcard search to show those. Create Query Design, bring in that CustomerT, and we're looking in the Last Name field. For the criteria, I'll zoom in so you can see this better. You could just say: Like "*DSC*", this has shown me anything that has "DSC" in it with any number of characters before and after that. If I run that now, there they are. But I am only seeing those ones and I do not see all the rest of the products, or in this case, last names. That is not going to be very helpful.
What we are going to use instead is we are going to make a calculated field. We are going to find whether or not that string "DSC" - that string of three characters - appears inside of the other string, which is Last Name.
Create a calculated query field. I will zoom in so you can see it here: Shift+F2 to zoom in. We will just call this X or whatever you want to call it. It will be: InStr([LastName], "DSC"). That is what you are looking for inside of Last Name. The InStr function will return a number from zero to whatever position that code appears inside of Last Name.
Let's see how it looks. Then run it. There you go. Zero means it does not appear. That five means that it appears at position five inside that string. See? One, two, three, four, five. Zero, there is a six, there is a four, there is a one. That works.
If you have null values, like if there is no value here, then the InStr function returns a null as well. Make sure that you put something in there. If not, you're going to get a null value. You can use IsNull if you want to wrap that in there.
If you want to make this be a true/false value instead of just a number, all you have to do is use that IIf function. You can do it in a separate field if you want to, or you can wrap it around this one. Either one works fine.
I am going to come over here and use a separate column. We will call this IsDisco. Now, I am not talking about disco dancing! IsDisco is going to be - let me zoom in so you can see better - IsDisco is going to be: IIf([X] > 0, True, False).
Now when I run it, there you go. You get zero is a negative one. Remember, zero is false, negative one is true.
If you want to format this to show true/false instead of -1 and 0, watch this trick. I am going to close this, right-click, go to Properties, go to the Format property here. Now, it is not in the list that you drop down, but watch this. You can type in "True/False" as the format. Now when I run it, look at that. You get "True"/"False" there. Or if you want, you like "Yes" and "No", do "Yes/No" like that.
Even the fast tips have other little fast tips in them. Imagine how many cool little tips and tricks are squirreled away inside my full courses. I teach all kinds of cool stuff.
I often show multiple steps like this because it is easier for beginners to comprehend, but we can actually get rid of a lot of this stuff. For example, we do not really need the IIf function. Watch this - let me get rid of this. We can say this is going to return that number. All we have to say here is: [X] <> 0. What is going to happen is this function will return a value that is either 0, a number, or null. If it is null, do not worry about that.
If this returns a number and that number is, let's say, 5, is that 5 equal to 0? No, it is not. Watch what happens. Look at that. You get a true or false value because this is technically an inequality. Is that number returned equal to or not equal to 0? Now we can format this as Yes/No, and we can call it here, if you want to, call this IsDisco.
I do not usually go straight to this step here. Honestly, when I am building stuff, even myself, I like to do it in multiple steps, in multiple columns, so I can see what I am doing. That is actually easier later on for you to look back on it and go, "What was I thinking?" You might look at this two years from now and go, "What does all this mean?" But if it is laid out in multiple steps, sometimes it is easier for you to comprehend. If you work in teams, it is easier for other people to comprehend.
Could you make it shorter? Sure, you can make it shorter, but it is not necessarily more readable. Access does not care. We are talking about milliseconds of time here. It is not going to make it any slower to have two columns here.
There you go, Freddie. That is how you do what you want to do. Now you know whether each one of these products is discontinued based on the fact that it has "DSC" in it. That was discontinued. That is that simple.
If you want to learn about more of these string functions, you have Left, Right, Mid, the Len function, and I do also cover InStr in this one. This is a free video - go watch my String Functions video.
If you really want to learn this stuff, I have a whole bunch of videos that are my comprehensive guide to Access functions, and Access Expert Level 25, we cover lots of different string functions. Here they are, lots of them, and logical functions. Then we go into math functions and type conversion functions. I cover all the functions. You want them? You want functions, baby? I have got them.
But in 25, that is where I cover the string functions, these guys. So check it out. I'll put links to all this stuff down below.
That is your fast tip video for today. I hope you learned something, and I'll see you next time.
Quiz
Q1. What is the primary use of the InStr function in Microsoft Access, as shown in the video?
A. To sort the records in a table alphabetically
B. To determine if one string appears inside another string
C. To auto-complete text fields while typing
D. To encrypt sensitive text fields
Q2. What was Freddie's main goal regarding the status codes in his data?
A. To delete all records with a "DSC" code
B. To see only records without a "DSC" code
C. To flag which records contain "DSC" in the status code but still view all records
D. To copy all records containing "DSC" to another table
Q3. Which wildcard query expression would display only those records where "DSC" appears anywhere in the field?
A. Like "DSC*"
B. Like "*DSC*"
C. "DSC"
D. Left([Field],3)="DSC"
Q4. What does the InStr function return if the substring "DSC" does NOT exist within the field?
A. -1
B. 1
C. 0
D. Null
Q5. How can you convert the InStr numeric output into a True/False (Boolean) value in a query?
A. Use the Format function
B. Use the Sum function
C. Use the IIf function to check if the value is greater than zero
D. Use the Len function
Q6. What happens if the field being searched by InStr contains a Null value?
A. InStr returns -1
B. InStr returns Null
C. InStr returns zero
D. InStr ignores the record
Q7. What can you type into the Format property of a Boolean field to make Access display "True/False" instead of "-1/0"?
A. Yes/No
B. Binary/Decimal
C. True False
D. Enabled/Disabled
Q8. What is a simpler alternative (described in the video) to using IIf for converting InStr results to a Boolean value?
A. Use the Len function on the field
B. Use [X] * 2
C. Use the inequality [X] <> 0
D. Use the DateDiff function
Q9. According to the video, why is it sometimes better to break calculations into multiple steps or columns?
A. It reduces system memory usage
B. It makes queries run faster
C. It makes the process more readable and understandable, especially for beginners and teams
D. It is required by Microsoft Access
Q10. What other string functions are mentioned in the video as useful for text manipulation in Access?
A. Sum and Avg
B. Left, Right, Mid, Len
C. Format and Round
D. Now, Date, Time
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 8-C; 9-C; 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 video from Access Learning Zone focuses on using the InStr function to find a string within another string in Microsoft Access. I received a question from a viewer whose vendor uses a status code that sometimes contains the characters "DSC" to indicate that a part has been discontinued. The challenge is to mark which parts have this code, even though the status code field might contain other unrelated details. The goal is not just to filter out records containing "DSC," but rather to display all records and indicate which ones are discontinued.
First, if you're not familiar with creating calculated query fields in Access or using the IIf function for conditional results, I recommend reviewing my earlier videos on those topics. Similarly, if wildcard searches in queries are new to you, check out those resources before moving ahead.
In this demonstration, I used my free TechHelp template database and pretended that the Last Name field in the CustomerT table was the vendor status code. I entered a variety of sample last names, making sure some contained "DSC" in different positions to mimic real-life scenarios where status codes might have various embedded terms.
To check whether "DSC" appears in the field, you could simply use a query with wildcard criteria like Like "*DSC*," which shows only records with "DSC" in the field. However, this only displays matching records and hides everything else, which is not what we want.
Instead, we need a calculated field in the query. By creating a new column and using the InStr function, you can check whether "DSC" appears anywhere in the field. The InStr function returns the position where "DSC" is found, or zero if it's not present. For example, if you see a result of 5, that means "DSC" starts at the fifth character in the field. If it's zero, "DSC" is not present. If the field is null, InStr will also return null, so make sure to handle that if necessary.
To display a simple true or false indicating whether "DSC" exists in the field, you can wrap the InStr function with the IIf function. This lets you return True if the number is greater than zero, and False otherwise. You can adjust this further by setting the format property of your query column to display "True" and "False" instead of -1 and 0, or use "Yes" and "No" if you prefer.
While I demonstrated this in several steps, beginners might find it clearer to break it down into columns for readability and easier debugging later on. Advanced users might combine everything into a more concise expression, simply checking if the InStr result is not equal to zero, which directly gives a true or false answer.
Even though you can streamline your queries, I often recommend using multiple fields when developing your own solutions, especially in collaborative environments. This makes it easier for you or your colleagues to follow your logic after some time has passed.
In summary, using InStr in a calculated query field is an effective way to identify status codes with embedded strings like "DSC" without filtering out other records. This approach gives you a simple way to mark records as discontinued right inside your list.
If you're interested in learning more about string functions in Access, such as Left, Right, Mid, Len, and others, be sure to watch my free String Functions video. For a more in-depth look, my Access Expert Level 25 course covers many string and logical functions, as well as math and type conversion functions. You'll find links to these resources on my website.
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
Using the InStr function to find substrings in Access
Creating calculated fields in a query
Identifying substring positions within a text field
Using IIf to convert numeric results to True/False
Formatting query output as True/False or Yes/No
Simplifying expressions using inequality checks
Handling null values in InStr results
Building step-by-step logical fields for readability
|
