Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenating 2 fields (Access 2000)

    I am trying to Concatenate 2 fields in an Access Table. FNAME and LNAME are the two fields. I have tried a Union Query, and I'm not getting the expression right. Is there an easier way to do this using a query. We exported to Excel, did the & " " & thing, and got the column just the way we wanted, concatenation went fine, then we tried to import that file back to access. The file went well, and we imported it as a new table. Then we tried to combine that new table with an existing table via a make-table query but it just wanted to duplicate itself over and over again, anyway.....to make a long story short....we know that it's simpler then this, and it can be done within access without traveling elsewhere. Thanks for your help. Also we will delete the LNAME and FNAME fields if we can ever come up with a combined field from the two.
    NMPAdgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Concatenating 2 fields (Access 2000)

    If you are trying to concatenate 2 fields (FNAME and LNAME) in table A into one field (CONCATNAME) in table A, then an update query will do this.
    UPDATE A SET CONCATNAME = FNAME & " " & LNAME
    This will update every record in table A.
    I hope I have not missed the point.
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Concatenating 2 fields (Access 2000)

    Pat is correct in terms of how to concatenate text strings. However you don't usually want to do it in a table - you do it in a SELECT query which assembles names, street addresses or whatever for report purposes or form display purposes. We nearly always separate names into several fields so we can do a Dear Mr/s LastName and also put a full address in using concatenation. Hope this makes sense.
    Wendell

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Concatenating 2 fields (Access 2000)

    Wendell is quite correct--there's no reason to combine, say, first name and last name fields in a table. I've made that mistake myself, and it took me forever, it seems, to understand why it's not wise to do this. It's also quite unnecessary. The table is little more than a container for your data. You don't print a table--you just store pieces and parts in it.

    If you're like me, you probably have a report or a form where you want to display the full name of the people whose data populates your database. Fine. I've discovered that I can concatenate first and last names right on the report itself--forget having to do it in a query! Oh, you can certainly do it in a query--nothing wrong with that. Here's an example: I have an eight-page report where I need to display the individual's name and payroll ID number in the footer of every page. I put a small text box in the footer. On the Properties sheet > Data tab > Control Source, I keyed in

    =[FirstName] & " " & [LastName] & " " & [PayrollID]

    That's all it took. I could have activated the Expression Builder, the better to see the entire string, but even that step was not necessary. You're far better off to leave the two fields separate. You'll have more flexibility to do other things. If you need to pull data to populate fields in a Word document, e.g., you might want to have just the first name available ("Just think, Steve, how good you'll look...") or the last name ("Dear Mr. Case:").

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating 2 fields (Access 2000)

    Okay, a little explanation is in order...sorry. This is not for me. It is for one of my students. She works for the Naval Hospitals business office, and for whatever reason, she did in fact need the names in the table combined. It's not up to me to disuade her, only see if I can assist. I agree, the time to concatenate is in the report, or in a form for appearance sake. But if there is one thing I have learned teaching Access over the last few years to different agency's (the Marine Corp Base to be more specific) it is that there are zillions of ways to use Access that I simply sit back and stare at (and try to hold my jaw shut at the same time). I'm in awe. Anyway - thanks a bunch...truly and will pass all your messages on to her (our class ended Friday), but did promise her to pass on your thread. As always, forever grateful.
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Concatenating 2 fields (Access 2000)

    Ah! Marines! That explains everything! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Just kidding! (See my profile....)

    I understand completely, and I didn't mean to insult--clearly you understand what I (and others) meant to say. Getting a student to "see the light" can be a tough assignment. Good luck!

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating 2 fields (Access 2000)

    Lucas,
    No insult taken. I rely on you guys here to help me each and every day, and would not know how to cope without you. It is unique to watch how they use the application. Especially Access. I have learned to keep a very open mind to their needs, and to their structures...in most cases, I have learned valuable insight and sometimes better ways to work with the app. And I always "eat" my words..."Where theres a will there's a way". I just may have to stop saying that so often. (Big Smile)...
    As always, Many, Many Thanks
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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