Filter Correct postal code

[Crilen007]

I live in Canada, Postal Codes here are V2V2V2

Is there a way I can apply a filter to make sure that there are letters in the correct letter spot and numbers are in the correct spot?

So anything thats not like V2V2V2 will show up for me to delete?

(V = Letter, 2 = Number, it doesnt have to be V and 2, it can be any number or any letter, but it has to be in that order, Letter, number, letter, number, letter, number.)

 

[sfreeman@co.mer]

Create an input mask for that field in your table.

"L0L0L0"

Should allow only Letter Digit Letter Digit Letter Digit

 

[Crilen007]

I need to filter it, It has already been entered, I imported this data from a mainframe and I am trying to clean it up.

 

[sfreeman@co.mer]

Sorry, I just saw where you want to DELETE all the bad data, not try to clean it up.

Will think about it.

 

[sfreeman@co.mer]

Hth

Ok, here you go. Probably a shorter way, but it should work.

DELETE
Table9.Postal

, Len([Postal])
AS Len

, Mid([Postal],1,1)
AS [First]

, Mid([Postal],3,1)
AS Third

, Mid([Postal],5,1)
AS Fifth

, Mid([postal],2,1)
AS [Second]

, Mid([postal],4,1)
AS Fourth

, Mid([postal],6,1)
AS Sixth

FROM
Table9

WHERE
(((Len([Postal]))<>6))

OR (((Mid([Postal],1,1)) In ("1","2","3","4","5","6","7","8","9","0")))
OR (((Mid([Postal],3,1)) In ("1","2","3","4","5","6","7","8","9","0")))
OR (((Mid([Postal],5,1)) In ("1","2","3","4","5","6","7","8","9","0")))
OR (((Mid([postal],2,1)) Not In ("1","2","3","4","5","6","7","8","9","0")))
OR (((Mid([postal],4,1)) Not In ("1","2","3","4","5","6","7","8","9","0")))
OR (((Mid([postal],6,1)) Not In ("1","2","3","4","5","6","7","8","9","0")));