Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Query Concatenation Issue - Access 2010

    I have a query in which I have concatenated the Last name, First name, and Middle initial fields. The Concatenation is as follows:
    Name: [tblStudentInfo].[LName]+", "+[FName]+" "+[MidInit]

    Often, I do not have the persons middle initial in the table. If I run the query, the People's names that DO NOT have a middle initial in the table come back blank. Is there a way to have the query return the names whether there is a middle initial or not?
    If I take the +" "+[MidInit] portion out I do get all the names, but I will eventually have many people that have the same First and Last names (e.g. John Smith), so I really need to have the Middle initial in the query.

    Thanks!

    Bart

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by bartputnam View Post
    I have a query in which I have concatenated the Last name, First name, and Middle initial fields. The Concatenation is as follows:
    Name: [tblStudentInfo].[LName]+", "+[FName]+" "+[MidInit]

    Often, I do not have the persons middle initial in the table. If I run the query, the People's names that DO NOT have a middle initial in the table come back blank. Is there a way to have the query return the names whether there is a middle initial or not?
    If I take the +" "+[MidInit] portion out I do get all the names, but I will eventually have many people that have the same First and Last names (e.g. John Smith), so I really need to have the Middle initial in the query.

    Thanks!

    Bart
    Don't use "+" for concatenation - always use the standard "&" operator instead. As you have seen, "+" can give you unpredictable results!

  3. The Following User Says Thank You to jeremybarker For This Useful Post:

    bartputnam (2012-09-13)

  4. #3
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I figured it would be something simple.

    Thanks for the quick response Jeremy!

Posting Permissions

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