Searching a string for to find a matching substring

[JoeA2006]

I am working with a field that contains a Description and 3 character code.
Normally the field contains a space between the description and the code.
Sometimes there are variations, but they are limited to the following

xxxxxxxx xxx (normal)
xxxxxxx xxx-x
xxxxxx - xxx
xxxxxxxx-xxx
I need to grab the 3 character code and bounce it of another table to
classisfy the record. Is there anyway to search the string for consecutive
characters that match the field in the other table that contains the data
related it the code. No matter how I try to manipulate the string I have not
found the answer..Obviously the answer is to have these descriptions uniform,
but until that happens, I hoped to work around it.

 

[Michel Walsh]

Hi,


You can use Replace to remove the spaces and the -:


UniformizedCode: Replace( Replace( myField, " ", ""), "-", "")



Hoping it may help,
Vanderghast, Access MVP