Query Criteria to return range of "not" records

[JPR]

Hello,
I have a table with 4 text fields in which the address of my clients is stored. The format of these fields is text and cannot chagne it because it contains address lines inclusive of zip codes. These codes are in all four fields. I would like to create a query that returns records which do not show the zip range, using something like not between 00000 and 99999. Do you think this is possible considering the text format of my data? Thank you

[meratigoerr]

Try using : Like "*#####" in your criteria, assuming that the zip is always the last part of the string.

2 cents: Addresses should always be divided into seperate fields. Street Address, city, state, and zip are all individual peices of data. Having them in seperate fields makes it a very simple matter to filter, sort, or find your nulls. I also have to question having 4 fields in a table that all contain seperate addresses. This is not proper normalization and also adds to the difficulty of searching for addresses. Consider putting your address in their own table and linking them to your main table thru a foreign key.

[JPR]

Thank you. Yes, I realize that with addresses it can be a problem. Unfortunately this info gets to me via a text file and I simply imported into a table. Each address fields can accept 22 charachters and the zip can be at any part of the string. I have tried your sample and works for one field but if I use it as criteria in the other address fields, I get all the records. Any additional help? Thank you.

[alansidman]

I concur with Meratigoerr on both issues. Separate your addresses into different fields and a separate table. Here is a tutorial on how to manipulate data with a query.

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2