Remove non numeric characters from field

[paulmitchell507]

My Access 2000 db contains [sites].[zip] which is a text field
containg zip codes, in the format of country code and zip code, i.e
DE40444 for a German address. I would like remove all the non numeric
characters from [sites].[zip] so it only contains numeric values.

Due to date entry error's, [sites].[zip] contains entries like
DE-40444 and 40444DE

I have very basic Access skills, so any help would be much
apprecaited.

Regards,

Paul

 

[Beetle]

See this knowledge base article;

http://support.microsoft.com/kb/210537

 

[KARL DEWEY]

Create a table named Alpha with field Letter containing all alpha characters.
Use in your query like this --

SELECT [sites].[zip], Replace([sites].[zip],[Letter],"") AS Plain_Zip
FROM StampIssue, Alpha
WHERE (((Len(Replace([sites].[zip],[Letter],"")))<Len([sites].[zip])));

 

[paulmitchell507]

Create a table named Alpha with field Letter containing all alpha characters.
 Use in your query like this --

SELECT [sites].[zip], Replace([sites].[zip],[Letter],"") AS Plain_Zip
FROM StampIssue, Alpha
WHERE (((Len(Replace([sites].[zip],[Letter],"")))<Len([sites].[zip])));

My Access 2000 db contains [sites].[zip] which is a text field
containg zip codes, in the format of country code and zip code, i.e
DE40444 for a German address.  I would like remove all the non numeric
characters from [sites].[zip] so it only contains numeric values.

Due to date entry error's, [sites].[zip] contains entries like
DE-40444 and 40444DE

I have very basic Access skills, so any help would be much
apprecaited.

Paul- Hide quoted text -

- Show quoted text -

Thank you, help much appreciated

 

[John W. Vinson]

One caveat. Do you have any UK addresses? Our postal codes are alphanumeric
(e.g. ST17 4PF in my case), so removing all the non-numeric characters would
leave some meaningless values.

The same is true of Canadian and many other countries' post codes. Assuming
that "zip codes" are numeric is perilous!