Results 1 to 13 of 13
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Concatenate Zip+4 (2002 SP-2)

    What a thing to deal with on Sunday morning.

    Anybody have a clue how to concatenate a Zip+4 code and still retain the " - " between the first five and the last four? I currently have:
    =[City] & ", " & [State] & " " & [Zip]
    Somewhere, CA 999999999 (Result)
    Somewhere, CA 99999-9999 (what I want)
    txtZip Input mask is 00000-9999;;_ This works just fine until I concatenate it. The underlying table, as well as all forms and reports, display the " - " just fine. It's only when I concatenate that it vanishes. This is for a report.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    Try :
    =[City] & ", " & [State] & " " & Format([Zip],"99999-9999")
    Francois

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    Francois:

    Thank you. I had tried that, but Access kept putting additional " in the line:
    =[City1] & ", " & [State1] & " " & Format([Zip1],"""99999-9999""")
    And this brings up a Zip+4 code of 99999-9999 for every address, rather than the actual Zip code. Hmmm. . .
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Concatenate Zip+4 (2002 SP-2)

    Why do you have the extra double quotes around the format string? I've never seen Access insert those, and the way you have it now, you're telling it to use the literal string rather than formatting the value using that string.
    Charlotte

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    Charlotte:

    Happy post-Vday! As I said, honestly, Acess is inserting the extra quotes. If I remove them, they are re-inserted as soon as I move off the line.
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    And if you use :
    <pre>left([Zip],5)& iif(len([Zip])>5,"-" & mid([Zip],6),"")</pre>

    Francois

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

    Re: Concatenate Zip+4 (2002 SP-2)

    I've never seen Access do that, so something odd is going on. Your subject refers to concatenating Zip+4. Is that what Zip1 contains? What datatype is the field, or is this a calculated field in a query? And *where* are you trying to use this formula?
    Charlotte

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    I realy mean :
    <pre>=[City1] & ", " & [State1] & " " & left([Zip],5) & iif(len([Zip])>5,"-" & mid([Zip],6),"")</pre>

    Francois

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    Francois:
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>. . .You're the man!
    Thanks friend, it works perfectly!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    Charlotte:

    Francois cured the issue. Thanks for the reply!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    Charlotte:

    It just occurred to me that you may still be interested in the "quotes" issue. I'm in the middle of invoicing right now but, if you're still interested, I'd be happy to discover all the details and get back to you this afternoon. Just let me know (if I don't hear from you I'll assume your curiosity is satisfied).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Concatenate Zip+4 (2002 SP-2)

    If this is something you see consistently, then it is certainly worth getting to the bottom of.
    Charlotte

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Concatenate Zip+4 (2002 SP-2)

    Charlotte:

    For your entertainment:
    rptInvoicesProcessed comes from qfltInvoicesProcessed, which is built on qryMasterAll and tblCustomers (relationship at txtCustAcctNo). The Zip1 reference is not a calculated field and is from the tblCustomers and has the following attributes:
    Data Type: Text
    Size: 50
    Input Mask: 00000-9999;;_
    The
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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