Searching a string for to find a matching substring

G

Guest

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.
 
M

Michel Walsh

Hi,


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


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



Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search substring in a string 4
Oracle Data Provider for .NET: Connection request timed out 0
Help with query searching within a string 4
Help with searching for a substring 2
How do I find quotation marks in a string using INSTR? String exa. 2
Searching a txt file for splitting into different worksheets 1
extract 3 numbers from a string 0
String Query 3

Top