Use the InStr Function to 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.

LEARN MORE:

PRE-REQUISITES:

LINKS:

COURSE:

BECOME A MEMBER:

ADDITIONAL RESOURCES:

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

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[kerrywsmyth]

This is a brilliant tutorial. I am just now learning that you can get a Yes/No result from an inequality statement in a Query. If you had asked me to to this I would have DEFINITELY used an iif statement.

[ryanw]

I did not know the True/False Yes/No format fast tip! Excellent!

[tajuddinidrisi]

Thanks Rost, you are given amazing tips and I am following you so that I can improve my skill

[kimyawyatt]

This is very helpful. Thank you! How can you have more than one comparison criteria that your searching for. I tried using OR, but it didn't work: InStr([Description], "Advance" OR "Reimb")=0. I used this in Conditional Formatting.

[joamigg]

Quick question: Is there a way filter out an entire list? To clarify i have an excel spreadsheet that’s linked to access (so the solution can be in the front end on access or the back end on excel). It’s a bunch of material numbers and each material number has shipment info (supplier, qty, date, etc.). This list compiles of all my material numbers that has been shipped since 2018 (raw data). Now i have a separate list of about 2000 material numbers that I want this same data for. It’s all in that set of raw data but to manually filter for these 2000 material numbers would be crazy. How can i take the list of material numbers that I need and make either access or excel give me the results of this raw data for just these material numbers? I’d love to learn how to filter out large sets of data on both excel and access, but answer for just one would do.

[afrinsir]

very nice
when I use DSUM IN query . it slow the query after many records
Is there any other option for running sum become more faster

[amrelnaggar]

I'm a big fan of your I have got a problem in my form in all fields are not counting how many characters inside I used one of your vba cide .. which is
private sub Mobile_no_afterupdate()
Msgbox "there are" & len(mobileno) & "characters"
End sub
Then whatever I write inside the field I get a message box says there are 0 characters .... I don't know what is the problem.... would you help me please?