Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Those pesky dashes in zip codes (ACC97-SR2)

    What's the best way to trim the trailing end off of a zip code field if it ends with the dash?
    (e.g 12345- but not 12345-6789

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Those pesky dashes in zip codes (ACC97-SR2)

    Charlotte,
    I can not get your expression to work. In my table, the ZipCode is a text field with an InPut Mask of 00000-9999. In the query, I added a field "Zip" and used you code. It removes all the dashes including the Plus 4 dashes. Have tried with and without Format or InPut Mask in the query. What possibly am I doing wrong?
    Jim

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Those pesky dashes in zip codes (ACC97-SR2)

    It depends on where you're doing it. If you're doing it in a query try:

    IIF(instr([zipcode],"-")=len([zipcode]), left([zipcode],5),[zipcode])

    Edited by Charlotte to correct expression
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Those pesky dashes in zip codes (ACC97-SR2)

    There was a missing paren in the expression, which I just fixed. Try it that way. What the expression is saying is that if the "-" is at the end of the value, then only use the first 5 characters. Otherwise, use the whole thing. It doesn't actually remove anything, so check your table and make sure whether the "-" is actually stored in your zipcode field or only displayed in the input mask.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Those pesky dashes in zip codes (ACC97-SR2)

    If you defined your zip code input mask in table as 00000-9999;;_ (symbols not saved) when you run query with the IIF expression the hyphens will vanish. You'd need to define input mask as 00000-9999;0;_ (symbols saved in table).

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Those pesky dashes in zip codes (ACC97-SR2)

    To Charlotte and MarkD,
    I found and corrected the missing paren. When I open the table, the dashes are shown, but then is that because of what Mark said about the symbols not being saved - only shown because of the InPut Mask.

    Working with your expression, I modified it to the following;

    zip: IIf(Len([zipcode])=5,Left([zipcode],5),Format([zipcode],"00000-0000"))

    This works in my case, but would like to know your thoughts.

    Thank You for the replies
    Jim

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Those pesky dashes in zip codes (ACC97-SR2)

    Check your field properties and see if you have a display format set to show the dash. Input masks are for forcing data entry into a pattern, but it isn't the same thing as the field's Format property, which controls how the saved value is displayed. It sounds to me like you're storing the zip code without the dash. Otherwise, you wouldn't need the Format function to make it appear in the query.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Those pesky dashes in zip codes (ACC97-SR2)

    Good Morning Charlotte,
    I only have the InPut Mask on the field property for the ZipCode field. And there are no Formats or Input Masks on the properties for the fields in the Query. Your guess on "storing the zip code without the dash" is correct. I created the Mask by useing the Wizard - go to the Wizard, select the Mask wanted and click Finished. Now, going back and following through the Wizard using the Next button, I found that on the next to the last screen there are two options that allow you to select how the data is stored - With or Without Symbols.

    This is where Mark stated that the Input Mask should be "00000-9999;0;_ (symbols saved in table)", where my Mask was 00000-9999;;_ (symbols NOT saved in table). So much to learn/remember. <img src=/S/frown.gif border=0 alt=frown width=15 height=15> <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    Thank You for your assistance,
    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •