Skip to Content
author's profile photo Former Member
Former Member

Expression - Zip Code validation

We have the field called Zip Code and this is a text field and 10 Chrs long.

Field 1: State

Field u2018Stateu2019 is a lookup field and the lookup table has Country, state code and state name.

Field 2: Zip Code

Zip Code = text field and the length is 10 chrs.

The ZIP Code Validation should be either NNNNN format (Block of 5 digits) for standard ZIP Code OR

NNNNN-NNNN format (Block of 5 and 4 digits) for ZIP+4 format

For example, if the country = US and state = IL then the first digit of the postal code field will be 6 then remaining 4 digits must be numeric for the standard 5 digit Zip code.

Can anybody help me to write the expression. Full points will be rewarded.

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jun 16, 2008 at 09:56 PM

    Hi JP

    For example, if the country = US and state = IL then the first digit of the postal code field will be 6 then remaining 4 digits must be numeric for the standard 5 digit Zip code.

    let me try. First use LEFT(ZIP,1) this will return first digit of ZIP code check if it is equal to "6".

    Use RIGHT(ZIP,4) to get the remaining 4 digits.

    Next task is to find out that all 4 digits are numeric. Use FIND and HAS ANY in combination (not sure).

    Simple option wud be to make ZIP as type Integer. No need for step 3.

    hope this helps.

    Regards-

    Ravi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 16, 2008 at 10:37 PM

    Thanks Ravi.

    But the we missed the first check whether the country = US and State code = IL. The Zipcode field validation is based on the country and state code of the record. That is Field 1, I explained earlier.

    "Field 1: State

    Field u2018Stateu2019 is a lookup field and the lookup table has Country, state code and state name."

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi JP

      validation statements should be written with generic execution in mind. we should avoid such specific value and field combination for executing validations.

      In this case use IF((country.lookup value =select value as US AND Sate code=IL) THEN valdation statement............)

      Award points if useful

      Best regards-

      Ravi

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 04:52 AM

    Hi,

    Try this it should work,

    For example, if the country = US and state = IL then the first digit of the postal code field will be 6 then remaining 4 digits must be numeric for the standard 5 digit Zip code.

    I am assuming here State = IL indicates state code = IL which is field in your Lookup table. As you said your Lookup table having fields Country, state code and state name

    IF(FIND(State.Country, "US") AND FIND(State.state code, "IL"), LEFT(Zip Code,1)=6 AND HAS_ALL_CHARS(RIGHT(Zip Code,4),0,9), FALSE)

    otherwise if your State having IL value is simple text field in maintable then replace it as

    IF(FIND(State.Country, "US") AND FIND(State, "IL"), LEFT(Zip Code,1)=6 AND HAS_ALL_CHARS(RIGHT(Zip Code,4),0,9), FALSE)

    Hope it will Help you,

    Rewards if Useful......

    Mandeep Saini

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 05:58 AM

    technically you can write a logical statement to find out if an address in Illinois has a zip starting with 6, but there are other states whose zip start with 6 (Nebraska, Kansas and more) which are not Illinois, so the most accurate would be to keep a combination of all possible first 2 digits of zip, and each combination should be associated with a single state, as far as I know.

    Another approach would be to use external zip validation as part of address validation and to check that not only the zip is valid within the state, but also agrees with the city, street and residence.

    See previous forum discussions about address validation.

    Edna

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.