Searching For Specific Characters

[mous]

Hello

I would like to be able to search for specific characters using a query. I use MS Access 97.

I would like to check the entry of postcodes for example, the correct postcode layout is (XXNN NXX) where X is a character (A-Z) and N is a number (0-9).

I want to identify incorrect postcodes that do not meet this criteria, for example 1NW 5P5.

Also,

I have a telephone field that can only contain numbers along with no spaces. Is there a *query* I can set up to remove mid or trailing spaces of any other characters.

Many Thanks
Dawn

 

[ColinEssex]

Hi Dawn

If you put an input mask on the postcode field the users will have to enter it correctly in the first place.

Col

 

[mous]

Sorry, this was my fault I should have explained myself better. I am accessing a third party SQL database through Access using ODBC. I have no control over this field in this first instance.

Thanks Dawn

 

[simongallop]

Look at the LIKE operator in help

Trailing spaces can be removed by TRIM
Mid spaces would have to be done by a combination of Find and mis / instr / left built into a function.