Results 1 to 5 of 5

Thread: Merging Fields

  1. #1
    Join Date
    Aug 2002
    Posts
    6

    Question Unanswered: Merging Fields

    not sure if this can be doen but i am hoping so. I am using Microsoft Access 2002 (XP) and i would liek to merge 3 fields into one.

    I am modifying a current table so that we can use it for another program. in its original state, the address is split into 5 fields:address1, address2, city, state, and zip

    however the new program requires it to be 3 fields: Address1, Address2, and Address3. where Address2 is the City State and Zip

    so can i merge the city, state and zip fields into one field and rename it to Address2???

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Yes, you can concatened field by using the & operator. You may want to add ", " or any other delimiters you want:

    Code:
    SELECT fldState & ", " & fldCountry AS NewName
    FROM tblSomeTable
    You can also use the same in an UPDATE query or into and INSERT

    JefB - hope it helps

  3. #3
    Join Date
    Aug 2002
    Posts
    6
    Originally posted by JefB
    Yes, you can concatened field by using the & operator. You may want to add ", " or any other delimiters you want:

    Code:
    SELECT fldState & ", " & fldCountry AS NewName
    FROM tblSomeTable
    You can also use the same in an UPDATE query or into and INSERT


    Well You got me started on the right track i think but i am not quite there yet.

    I want to merge the data from 3 fields (City, State and Zip) into another field(Address3)

    so based on what you told me i came up with the following query, which did nothing.

    UPDATE Vendor
    SET Address3 = City & ", " & State & " " & Zip

    my thought was that would go through and update Address 3 with "City, State Zip" in all records. it did nothing though.

    sorry if this is a dumb question but i am kinda new to this.

    what am i doing wrong?

  4. #4
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Hum... strange, I've just try it and it work well.

    So try that, create a new Query, don't use any assistant, go right to the SQL Text and paste that:

    Code:
    UPDATE Vendor SET Vendor.Address3 = City & ", " & State & " " & Zip;
    Save your query, name it as you awnt, then EXIT, don't run it, now, after exiting, doubleclick on it, Access will ask you "Are you sure you want to Update your table", then "It will update X lines, do you want to continue", answer yes, and go see your table. If it don't work... , let us know, there is surely something amissing!

    JefB - hope it helps

  5. #5
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    Make sure that all the fields are of same data type else need to use UPDATE Vendor SET Vendor.Address3 = cstr(City) & ", " & cstr(State) & " " & cstr(Zip);

Posting Permissions

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