Results 1 to 10 of 10
  1. #1
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86

    Concatenate Fields

    I am joining fields =[Fname] & " " & [Minitial]+". " & [LName] This eliminates the (.)period and the extra space, when you do it directly in a form, but when I do the same thing in a query it does not work. Any suggestions?


    Thanks

  2. #2
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254
    Njliven -
    In a query, you could try...

    FN: [FName] & " " & IIf(IsNull([MInitial])=True,"",[MInitial] & ". ") & [LName]

    All the best,

    Jim

  3. #3
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Thanks for the suggestion however, it is still not working. Any other suggestions?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    What does this really mean
    I am joining fields =[Fname] & " " & [Minitial]+". " & [LName]
    Are you trying to concatenate these?? Join has a whole different meaning in database.

    , it is still not working
    Do you get an error?
    Can you show us an example of
    a)what you are using
    b) what you want to see, and
    c) what you are actually getting?

  5. #5
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    On a form when combining the name fields, I used the + after the Minitial and it does not put the period and the extra space if the Minitial is null (which is what I want to happen).
    However if I use the same formula in a query it just ignores it. When the Minital is null I get this: John . Doe I want to see John Doe.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    So you would really like some expression that will work in a query and a form.
    My best guess

    Query

    Fullname: Iif(Len(MInitial) & "" = 0,FName & " " & LName, FName & MInitial & ". " & LName)

    For the form control
    =Iif(Len(MInitial) & "" = 0,FName & " " & LName, FName & MInitial & ". " & LName)

    Good luck.

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by Njliven View Post

    =[Fname] & " " & [Minitial]+". " & [LName]

    ...I do the same thing in a query it does not work...
    Are you actually using the exact same thing, in the Query, i.e.

    =[Fname] & " " & [Minitial]+". " & [LName]

    Because in the Query Design Grid you can use the same basic expression, but you have to drop the Equal Sign and give the Calculated Field a name, say 'FullName'

    FullName:[Fname] & " " & [Minitial]+". " & [LName]

    In the SQL Statement it would be

    [Fname] & " " & [Minitial]+". " & [LName] AS FullName

    Linq ;0)>

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847

  9. #9
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    This one does the trick. Thanks!

  10. #10
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Glad we could help!

    Linq ;0)>

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Merge fields (not concatenate)
    By sberti in forum Access
    Replies: 2
    Last Post: 11-30-2012, 12:53 AM
  2. Replies: 1
    Last Post: 11-01-2011, 05:55 PM
  3. Replies: 3
    Last Post: 08-16-2011, 02:20 PM
  4. Creating Query from Code, Concatenate fields
    By eww in forum Programming
    Replies: 5
    Last Post: 07-18-2011, 02:19 PM
  5. Concatenate two fields (text & number) for key field
    By Larry Elfenbein in forum Forms
    Replies: 2
    Last Post: 11-10-2005, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums