Use String Functions to Validate Part Numbers in Microsoft Access: Left, Right, Mid, Len, InStr

In this video I will teach you how to use the string functions in Microsoft Access to make sure users are entering valid part numbers according to a set of rules. We will learn about the Left, Right, Mid, Len, and InStr functions.
Zoe from Great Falls, Montana (a Platinum Member) asks: When placing an order with our primary supplier, we have to make sure the part number is valid. They have a gigantic database of parts, and it changes constantly, so looking up each part number isn't practical. They do, however have a specific format for all of their part numbers. If I could just make sure my users are typing the part numbers in the correct format, that would eliminate 99% of my problems. I import most of this data from Excel sheets, so I can't use an Input Mask. You wouldn't believe the junk I have to correct! Can you help me, please!?
Part numbers have to start with the letter P. The right 3 characters have to be a number greater than 100. The 5th character has to be a hyphen. The whole string has to be exactly 9 characters long. The letter X must be included somewhere in the string.
Silver Members and up get access to an Extended Cut of this video. Members will learn how to use the string functions in VBA to make sure users are entering in valid passwords. We will learn how to do a binary search (to tell capital from lowercase letters) with Option Compare Binary or StrComp function, and to determine if a string contains ANY character from an allowed list. We'll write our own IsFound function - is any character from string 1 found inside of string 2.
Leon from Bismark, North Dakota (a Gold Member) asks: I created a form allowing my employees to change their password. They must enter their current password and then create a new password, and verify the password by entering it twice. I check that the new password is not the same as the current password. I want to include all the parameters that strengthens the password. I appreciate your help.
The first character has to be a capital letter. The password must contain both capital and lowercase letters, plus at least one numeric digit and one special character: !-_#$@%+
MEMBERS VIDEO:
• String Functions - Cha...
BECOME A MEMBER:
KZbin: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/StringFunctions
LINKS:
IIF Function: 599cd.com/IIF
Calculated Query Fields: 599cd.com/Calculated
Conditional Formatting: 599cd.com/Conditional
Access Expert 25 String Functions: 599cd.com/ACX25
Simple Security: 599cd.com/SimpleSecurity
Security Seminar: 599cd.com/Security
COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Paid TechHelp Questions: 599cd.com/TechHelpPaid
Consulting Help: 599cd.com/Consulting
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, left, right, mid, len, instr, strcomp, part numbers, change passwords, option compare binary, capital, lowercase, numeric, digits, one string found inside another, isfound, any character found in a string, allowed characters list
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@petewarby7158]

Omg is there nothing you don't know. This series of tutorials are EXCEPTIONAL

[@homeheart1276]

[2:38] "I know you need this for real work. The rest of us are just goofing around" lol! This is one of the reasons why I love you man. Thank you :)

[@599CD]

8-)

[@luzkrg]

Really interesting, thank you! 🤩

[@599CD]

My pleasure!

[@jstan3076]

Thank you , Professor Access , it help me too

[@599CD]

You're welcome

[@user-dz8uv8ks4h]

Hi Richard! First of all, I learn A LOT from you! Thank you so much for your educational videos. I have a little problem that it not seems to be solved with these 5 methods and I bet you can help: I need to find all the records that contain fructure of a string from another string, in a case like this: "ABCDEF" - will return "ABC", "BCD", "CDE" and so on... if they are exist... I tried to figure it out, with no luck.. Thanks a lot in advanced!!!

[@599CD]

Sounds like a job for InStr

[@gregorytaylor2749]

Good video! I work a lot with string validation, so I sometimes write an expression like: IsValid:IIF(P LIKE "[P]???-?[1-9]##" AND P LIKE "*X*","VALID" , "*INVALID*")

[@599CD]

Thanks for sharing!

[@msiitian4064]

Great

[@599CD]

thx

[@artistryartistry7239]

Thanks! What would be the syntax for the criteria entry on a particular field in query designer? Len([Field Name])

[@599CD]

Put Len(FieldName) up top and the

[@adamsanaf]

Thanks Richard. Helpful tutorial. I hope i could pick your brain abit. I am planning to create a database with a scanning feature. The thing is when i scan the dmc code, it gives out a 48 character string and out 48 characters, i need only two parts of it, i.e the 26th to 35th characters and the 40th to 48th characters. The first one acts as a foreign key while the second one acts as a entry name. Do u know how could i extract these numbers from the string? Thank you in advance

[@599CD]

Pick here: 599cd.com/ask

[@Thanogr1]

Gee thanks for the idea of breaking down the solution

[@599CD]

You're welcome

[@sudipsukantapaul9109]

How to make Part Number like following format :- YYYYMMDD-001 001, 002, 003 and so on.... On every new day. Please help

[@599CD]

599cd.com/Counter

[@botskie2354]

Good day sir, I wanna ask if is it possible to make this in access if I Have an item with amount 5000.00 and paying for 3 years... And I want to calculate and auto matically input in a table or query ... Like example of this client credit 5000 wnd every month is showing like this , jan-1-21 - 138.88, feb .1 -21 138.88 , mar.1,21- 138.88 And it will auto matically end Until reach the 5000.00 I hope you read this Thank you and have more subscriber ....

[@599CD]

Of course it's possible. Is this an amortized loan, or just a simple non-interest payment? If the former, see 599cd.com/amort

[@RitaFonsecaSanchez]

No funciona, sale error

[@599CD]

Huh?