Results 1 to 4 of 4
  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

    Concatenation (2003 SP-2)

    Remarkably, I've finally gotten around to creating a form for company information where any changes will appear on all reports. I felt the best way to display single line information was with concatenation, but I'm having "issues" with phone/fax numbers and zip codes.

    (999) 555-5555 appears as 9995555555
    93721-0963 appears as 937210963

    The current concatenation is:

    =[CompanyAddress] & " " & [CompanyCity] & ", " & [CompanyState] & " " & [CompanyZipPlus4] & " Voice " & [CompanyPhone] & " Fax " & [CompanyFax] & " " & [CompanyEmail]

    Access seems to think my attempts to enter the characters "(" and "-" are rather humorous. Any help greatly appreciated!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Concatenation (2003 SP-2)

    Do you have Input Masks on the phone and zip fields? If so, a phone number such as (415) 627-1685 may actually be stored as 4156271685. To display it correctly in a concatenation, you can use the Format function:
    <code>
    ... & Format([CompanyZipPlus4],"00000-0000") & ...
    </code>
    and
    <code>
    ... & Format([CompanyPhone],"(000) 000-0000") & ...
    </code>
    The backslash before the - in the format string indicates that the - is a literal character, not a minus sign for negative numbers.

  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: Concatenation (2003 SP-2)

    Hans:

    Thanks friend! I actually was able to locate a post back in '04 for the zip:
    & left([Zip],5) & iif(len([Zip])>5,"-" & mid([Zip],6),"")
    Guess who posted it? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Your solution is better by far!!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Concatenation (2003 SP-2)

    The expression

    & Left([Zip],5) & IIf(Len([Zip])>5,"-" & Mid([Zip],6),"")

    is useful if the Zip field may contain both 5 digit zip codes and 5+4 digit zip codes. The Format function from my reply doesn't take 5 digit zip codes into account.

Posting Permissions

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