Avatar of Michael Noze
Michael Noze
 asked on

If a field contains a certain characters then

Hi Experts,

I'm looking for a function in VBA to use in my Access.
The function would return a string if a field contains a certain characters.


If field contains "i1" then in then result column we would have "inspection 1"
else if field contains "aa" then we would have "aircraft available"
else if field contains "e1" then we would have "elevator 1"
Microsoft AccessVBA

Avatar of undefined
Last Comment
Michael Noze

8/22/2022 - Mon
Paul Cook-Giles

Create a lookup table (LookupTb)  with two columns:  Code (short text, 2 characters) and Definition (short text, 25 characters).
Populate the table with your codes and definitions.

Create a custom function:

Public Function ReturnDefinition(strCode as string) as string
   ReturnDefinition = DLookup("Definition", "LookupTb", "Code = '" & strCode & "'")
End Function

Open in new window

John Tsioumpris

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Dettman (EE MVE)

Or just use a series of Replace() statements

Anders Ebro (Microsoft MVP)

It depends on bit. If by contains you mean that it is equal to the value, example the field is EQUAL to "e1", then I would use a table (as Paul suggested) to store the combinations, and then join the tables, in a query, or use a combobox. But I wouldn't normally use a dlookup, due to performance reasons (if you have thousand of records), especially if you potentially want to filter on the field.

If you mean that it contains the value, as well as other information e.g. the field is "e1 XXXXXXXsome more info"  then it is more complicated, as we could get alot of false positives.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Michael Noze


What I mean by contains is if the field contains the string. I also have over 48k records.
Field value:  XxxxXe1xxxx then => e1
I'll figure something out with the solution of John.

Thank you!