Link to home
Start Free TrialLog in
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"
Avatar of Paul Cook-Giles
Paul Cook-Giles
Flag of United States of America image

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

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or just use a series of Replace() statements

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.
Avatar of Michael Noze
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!