Share via

Access query for number input starting with zero

Anonymous
2021-02-11T19:55:46+00:00

Using Access 2016 on Windows 10.
I downloaded a table of zip codes.
The zip code field, in the table, is formatted as:
Field Size = Double
Format = 00000
I wrote a simple query for this table:
SELECT ZipCode.Zip, ZipCode.City, ZipCode.STATE
FROM ZipCode
WHERE ZipCode.Zip Like [Enter Zip Code];
No formatting/properties was applied to the query.
The problem is with zip codes that start with zero (as in New Jersey zip codes).
When entering a zip code, as prompted by the query, that starts with 0 (e.g. 08873 for Somerset, NJ), I get no results.
However, if I enter the zip code without the leading zero (entering 8873), then I get results. Including Somerset, NJ.
How can format/change the query to accept, and recognize, a leading zero? Preferably without having to define the field as "TEXT".

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-02-11T21:15:33+00:00

    The formatted values you see are not the actual stored values, so, as Hans says, you need to change the data type to Short Text.  Zip codes are not really numbers, having no ordinal, cardinal, or scalar significance; they are an encoding system.  They just happen to be made up of numeric characters.

    Once you have changed the data type, execute the following UPDATE query to ensure that all the values are of five characters length:

    UPDATE ZipCode
    SET Zip = FORMAT(Zip, "00000");

    It goes without saying that before making global changes like this it's imperative that you back up the table.

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2021-02-11T21:30:02+00:00

    To add to the responses from Hans and Ken, the LIKE operator is for finding patterns within character strings, not in numeric values. When used with a numeric field the DBMS is converting the value to text behind the scenes, so it appears to find numeric values "like" the text value you use.

    In my experience it is unfortunately too common in tables designed by people who will not be using the data to use numeric data types for zip codes, SSNs, UPC codes, and other data elements which only use numeric characters and can have leading zeros, rather than validating data entry to only allow numeric characters.

    1 person found this answer helpful.
    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-02-11T19:59:09+00:00

    I fear that you will have to change the zip code field to a short text field. You aren't going to use it for numeric calculations anyway.

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2021-02-13T04:01:48+00:00

    Thank you for the clarification.

    It makes a lot more sense now.

    Also, gives me a better understanding for future reference.

    0 comments
  5. Anonymous
    2021-02-13T04:00:19+00:00

    Ken,

    Thank you for the answer. Especially for the format update to the field.

    Will keep this in mind since this was, originally, an Excel spreadsheet which was imported into Access.

    And, most likely, where the number/text, problem came from.

    0 comments