Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Unanswered: Help with query concatenate

    My table has [Firstname][MI] [Lastname]
    --- [MI] has a input mask of >L"." so it Caps and puts a . after it

    Works great in a table and reports

    Query and concatenate the fields and it leaves off the Period after the MI

    Fullname: [Firstname] &" "& [mi]&" "&[lastname]
    = Frank F Farkle
    should be Frank F. Farkle

    If you use "." after the MI it will give a period when no MI exists

    Thoughts?

    Peter

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    IIf.

    Fullname: [Firstname] & " " & IIf([MI]>"", [MI] & ". ","") & [Lastname]

    Or, just leave it without the dot
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Or

    Fullname: [Firstname] & " " & ([mi] + ". ") & [lastname]
    Paul

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    But...
    If you use "." after the MI it will give a period when no MI exists
    I also hate to use "+" for a concatenation.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you're implying that my solution will not work, as you seem to be, I would point out that "+" will propagate Nulls, while "&" will not. Thus, that method will not display the period when the middle initial is Null. While I normally don't use "+" either, I take advantage of this difference in this situation.
    Paul

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Serves me right for having a vendetta against concatenating with + ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Simple Fix

    I used ([mi] + ". ")

    Works great, a simple task can be frustrating sometimes!

    Peter

Posting Permissions

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