Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    concatenation: unwanted comma

    Hi All,
    Say I have the fields; FirstName, MiddleName, LastName. In a query I'm trying to concatenate to [LastName, FirstName MiddleName]. There is, however, a slight problem....sometimes there is only a FirstName, sometimes there is only a LastName and most times there are both. My problem is that no matter how I've tried to do this, whenever there is only a LastName or FirstName I get a comma with one or the other. For example:

    using:
    LastFirst: [LastName] & (", "+[FirstName]) & (" "+[MiddleName])
    yields (with LastName only):
    Doe
    yields (with FirstName only):
    , Jane

    using:
    LastFirst: ([LastName]+", ") & ([FirstName]+" ") & [MiddleName]
    yields (with LastName only):
    Doe,
    yields (with FirstName only):
    Jane

    I've also tried various incantations <img src=/S/wartgun.gif border=0 alt=wartgun width=73 height=24> using IIf, but with the same results.
    How can I subdue that renegade comma?

    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: concatenation: unwanted comma

    Gary

    Have you tried an IIF statement?

    IIF( LastName="", [Firstname]& "," & [MiddleName], [LastName] & ", " & [FirstName] & " " & [MiddleName])

    as there are a few permutations you could nest them.
    Jerry

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: concatenation: unwanted comma

    Hi Jerry,
    I have tried the IIf function, not in the way you suggested though.
    I've been thinking linearly. I hadn't thought to nest the statements.
    I'll try it this evening and let you know.

    Thanks.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: concatenation: unwanted comma

    If you want to take all situations into account, even having a middle initial without having a first name, you can use this:

    =[LastName] & IIf(Not IsNull([LastName]) And Not IsNull([FirstName] & [MiddleName]),", ",Null) & [FirstName] & IIf(Not IsNull([FirstName]) And Not IsNull([MiddleName])," ",Null) & [MiddleName]

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

    Re: concatenation: unwanted comma

    You may have to write a function in a module to do this.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: concatenation: unwanted comma

    Hi

    What about just searching for and stripping out the unwanted commas. Perhaps, something like:

    Xname = LastName & ", " & Firstname
    Xstr = IIf(Left(Trim(Xname), 1) = "," Or Right(Trim(Xname), 1) = ",", Replace(Xname, ",", ""), Xname)

    Regards
    WTH

  7. #7
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: concatenation: unwanted comma

    Hi Hans,
    This looks a whole lot neater than what I was coming up with.
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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