If a field contains a certain characters then

Michael Noze
Michael Noze used Ask the Experts™
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"
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

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

Software & Systems Engineer
If it "contains" you could use Instr in a similar manner as Paul's comment

If Instr(Yourstring,Yourlookup) then
' Replace with what you want
End if

Probably iterate the records with a Recordset
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

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.



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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial