Ignore spaces in criteria

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I need to run a query off a linked table (that I do not
have rights to change) where the part numbers were not
entered in the same format. For example, the following
part numbers would be considered the same part
123 45
12-345
12345
1 23-45
And any other combination where spaces and dashes are
added to the base part number. (actual part numbers
include letters and numbers) My list of part numbers that
the query will be run from do not have any spaces or
dashes.

Part 2:
Some of the part numbers in the linked table have extra
characters in them. For example, a part number that I
consider to be 1234abc would be in the linked table as
1234Vabc. Once again, there is no pattern to the
additional characters.

Any ideas or suggestions?
 
Dear Steve:

You could use REPLACE() to ignore characters . (period), - (dash), and
spaces. Replace them with an empty string. Test this in a query.
The extra characters will dieappear. Depending on your version of
Access, you will probably have to make your own Replace function out
of the one that comes with Access in order to use it in Queries.
Microsoft didn't get that part working yet.

As far as there being other characters to ignore, and not knowing what
they may be or when and where they may occur, that sound very tough,
perhaps impossible. What if I have a part number 1234ab. Does that
match 1234Vabc as well?

If you have a comprehensive set of rules of where and what characters
may be inserted that are not significant, you may be able to do this
reliably, but there is no guarantee of that. The mess you inherited
may actually not be usable as a database, depending on the level of
ambiguity that has been built into it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top