Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports problem with zip codes (A2k)

    Here's my problem:
    A customer wants the ability to have 5 and 7 digit ZIP codes on a report. I have some code in the program that *worked*. However, when a new address record is inserted the code falls apart, and instead of showing only 5 digits, it shows 5 digits, a "-", the last 3 digits of the ZIP and another "-" (12345-345-). Here's the code that I currently have in the query that the address is called from:

    Formatted_ZIP: (IIf(Len([ZIP])>5,Left([ZIP],5)+"-"+Right([ZIP],4),Left([ZIP],5)))

    Help!?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reports problem with zip codes (A2k)

    In your example what is in the field Zip for it to show (12345-345-)?

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports problem with zip codes (A2k)

    following that example, i put in 12345

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reports problem with zip codes (A2k)

    Give us a few examples of what you keyin and what you wish to show, then we may be able to help.

  5. #5
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports problem with zip codes (A2k)

    When I input a ZIP code on the form, I tab/click on the box and input "12345". The box allows for a 9 digit ZIP (12345-6789) (sorry about the mixup before, I meant 9, not 7). I found out my problem is that Access counts those last 4 spaces. What changes should I make to my code to allow for the pre-entered ZIP codes and for all new ZIP codes.

    EDIT: I found out a way to solve this problem by going into the table and re-keying the ZIP code. What steps can I take to prevent Access from doing this when my customer inputs new records?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports problem with zip codes (A2k)

    Try this expression:

    Formatted_ZIP: IIf(Len([ZIP])>5,Left([ZIP],5) & "-" & Mid([ZIP],6),[ZIP])

  7. #7
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports problem with zip codes (A2k)

    It comes out "12345--"

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports problem with zip codes (A2k)

    And this one?

    Formatted_ZIP: IIf(Len([ZIP])>5,IIf(InStr([ZIP],"-"),[ZIP],Left([ZIP],5) & "-" & Mid([ZIP],6)),[ZIP])

  9. #9
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports problem with zip codes (A2k)

    that comes out "12345-"...anyway to get rid of that "-"...the customer is a VIP and whatever they want, they get <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reports problem with zip codes (A2k)

    Hans solution should work. Have you a mask on the field in the table, could this be interfering with it?
    If you keyin 12345 (no trailing spaces) you should get 12345 after Hans code.
    If you keyin 123456 you should get 12345-6.
    Is there anything you have not told us?

  11. #11
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports problem with zip codes (A2k)

    doh...that might be it...thanks

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reports problem with zip codes (A2k)

    Let us know what it was, it always helps us to know the answer.

  13. #13
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports problem with zip codes (A2k)

    It was the format masks that kept putting the "-" in the output. I realized that it wasn't necessary for them to be there, and removed them from the table and the form. Thanks again for all your help.

Posting Permissions

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