#1
  1. No Profile Picture
    JPR
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Sep 2005
    Posts
    379
    Rep Power
    10

    Query Criteria to return range of "not" records


    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
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    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.
  4. #3
  5. No Profile Picture
    JPR
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Sep 2005
    Posts
    379
    Rep Power
    10
    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.
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    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
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.

Similar Threads

  1. U.S. to unveil criteria for picking "systemic" firms (Reuters)
    By RSS_News_User in forum Business News
    Replies: 0
    Last Post: October 11th, 2011, 12:00 AM
  2. Replies: 0
    Last Post: November 20th, 2010, 02:00 PM
  3. Replies: 1
    Last Post: November 18th, 2009, 12:12 PM
  4. Query a string for "today's records"
    By jacosta00021 in forum Microsoft Access Help
    Replies: 5
    Last Post: June 23rd, 2005, 03:26 AM
  5. "Query is too Complex" and "System Resource Exceeded"
    By markdille in forum Microsoft Access Help
    Replies: 2
    Last Post: December 23rd, 2004, 12:40 PM

IMN logo majestic logo threadwatch logo seochat tools logo