Clean numbers

[Rick5k via AccessMonster.com]

I am going to try this again. I have posted messages about this but I find
that my knowledge base is not sufficient enough to be able to implement the
code. I have a database in which I have imported tables from two different
databases which uses different methods of entering the same numbers. One is
relatively clean and the other has numbers which uses dots, dashes, slashes,
and an occasional + sign. I need to remove these non word/numeric items so I
can work with both sets of numbers. An example of 1 of the 9000 records is
AB123-45 D/65. I want it to end up like AB12345D65

What if I brought into a query just the field I need to clean up and also a
field to allow me to match it back up with the original query. Then my SQL
for that query would look like this:

SELECT PMC.Part, PMC.[Vendor Part #] FROM PMC;

Then I would just need the correct wording to remove all the dots, dashes,
and slashes from the 9000 parts listed under Vender Part #
By the way, our system here uses Access 97 which is driving me crazy.

 

[KARL DEWEY]

Try this --
Replace(Replace(Replace(Replace(Replace([Part],"+",""),".",""),"-",""),"
",""),"/","")

Add other puncuation if needed.

 

[Rick5k via AccessMonster.com]

I've tried that but Replace does not work as a function is 97, unless I'm
missing something and the company has no plans in the foreseeable future of
doing an upgrade.

Try this --
Replace(Replace(Replace(Replace(Replace([Part],"+",""),".",""),"-",""),"
",""),"/","")

Add other puncuation if needed.

I am going to try this again. I have posted messages about this but I find
that my knowledge base is not sufficient enough to be able to implement the

[quoted text clipped - 14 lines]

and slashes from the 9000 parts listed under Vender Part #
By the way, our system here uses Access 97 which is driving me crazy.

 

[KARL DEWEY]

Your can go through a bunch of updates like this --

Left([Part],InStr([Part],"-")-1) &
Right([Part],Len([Part])-(InStr([Part],"-")))

Expr2: InStr([Part],"-")
With criteria >0

The first concatenates the part of the string to the left of the puncuation
with the part that is to the right of it. If there is more than one then you
will have to run the query multiple times and edit it for the next puncuation.

The second item pulls only those records that have the puncuation.

I've tried that but Replace does not work as a function is 97, unless I'm
missing something and the company has no plans in the foreseeable future of
doing an upgrade.

Try this --
Replace(Replace(Replace(Replace(Replace([Part],"+",""),".",""),"-",""),"
",""),"/","")

Add other puncuation if needed.

I am going to try this again. I have posted messages about this but I find
that my knowledge base is not sufficient enough to be able to implement the

[quoted text clipped - 14 lines]

and slashes from the 9000 parts listed under Vender Part #
By the way, our system here uses Access 97 which is driving me crazy.