Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    33

    Unanswered: Seems so easy: want to combine address into one field

    I have address kept as several fields; address,city,state etc

    I want to change them to they are all in one multi line field. I figured I have to export, manipulate (in excel or something) then reimporting.

    Any thoughts?

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Why?

    If you ever have to search by these field, keeping them seperate is much more efficient.

    When you display them in queries or reports, you can always combine them at run time.

    If you really want to do this, create a new field in your table. write a query that will combine your field together and write back to the new field

    UPDATE Table1 SET Table1.CityStateZip = [Table1]![City] & ", " & [Table1]![State] & " " & [Table1]![Zip];


    Then delete you old fields that you don't need

    S-

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    What is the field you want to place the data into? Is it a table field or a Form control such as a TextBox?

  4. #4
    Join Date
    Feb 2004
    Posts
    33
    Well, I only have a couple of hundred records, so only dealing with one field instead of 4 or 5 seemed easier. Making forms/labels and such would take 1/5 the time . I will probably do this more often. Also might save a tiny bit of time entering data (maybe).

    They are stored as fields in a table.

  5. #5
    Join Date
    Feb 2004
    Posts
    33
    UPDATE Table1 SET Table1.CityStateZip = [Table1]![City] & ", " & [Table1]![State] & " " & [Table1]![Zip];
    but but but... wait a sec

    This doesnt have the new address field with the city, state and zip on seperate new lines does it??

  6. #6
    Join Date
    Feb 2004
    Posts
    33
    ok, so I try this:

    UPDATE Consultants SET Consultants.ConsultantAddress = [Consultants]![ConsultantAddress1] & " " & [Consultants]![ConsultantAddress2] & "," & [Consultants]![ConsultantCity] & "," & [Consultants]![ConsultantState] & "," & [Table1]![ConsultantZip];

    and I get an error, it says operand/opertor missing and/or text not in quotes....

    ?

  7. #7
    Join Date
    Feb 2004
    Posts
    33
    bump

  8. #8
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by northbrit
    ok, so I try this:

    UPDATE Consultants SET Consultants.ConsultantAddress = [Consultants]![ConsultantAddress1] & " " & [Consultants]![ConsultantAddress2] & "," & [Consultants]![ConsultantCity] & "," & [Consultants]![ConsultantState] & "," & [Table1]![ConsultantZip];

    and I get an error, it says operand/opertor missing and/or text not in quotes....?
    Okay...

    First of all, I really think you are making a mistake by doing this... It's fine to combine fields that go on one line, but it doesn't save time... It adds the greater possibility of errors in your data... Think about it... A user opens a form to input a new person's address... How much more likely that they'll forget to put the City in... Or the Suite number?... If they have a separate text box to add it to... or if it's part of one long field?? ... Just my two cents... lol

    As for creating the value on separate lines... I played around with this yesterday because I knew about the Chr() function... Here's what it says in Access Help (I looked up ASCII character code list)...

    * * Values 8, 9, 10, and 13 convert to backspace, tab, linefeed, and carriage return characters, respectively. They have no graphical representation but, depending on the application, can affect the visual display of text.
    I tried concatenating in Chr(13) where the carriage returns should be... and Access accepted the field values without error... However, I couldn't find a situation where the data actually showed up on separate lines... I tried a text box on a form...

    I'll let you know if I find out anything else...

  9. #9
    Join Date
    Feb 2004
    Posts
    33
    lol Trudi, you are probably right. I am second guessing myself now. However, as I am working on the DB I have found another couple of situations where I needed to combine fields on seperate lines. Rather than change the exmaple and confuse people, I just kept with the address one .

    I have been wrestling with the same thing about char(11) in excel/access :/

  10. #10
    Join Date
    Nov 2003
    Posts
    267
    Originally posted by Trudi

    I tried concatenating in Chr(13) where the carriage returns should be... and Access accepted the field values without error... However, I couldn't find a situation where the data actually showed up on separate lines... I tried a text box on a form...

    It will only show up on seperate line when you put the data into a text file.



    Originally posted by northbrit
    ok, so I try this:

    UPDATE Consultants SET Consultants.ConsultantAddress = [Consultants]![ConsultantAddress1] & " " & [Consultants]![ConsultantAddress2] & "," & [Consultants]![ConsultantCity] & "," & [Consultants]![ConsultantState] & "," & [Table1]![ConsultantZip];

    and I get an error, it says operand/opertor missing and/or text not in quotes....?

    You may also get this error if there is a single or double quote in the data of your DB. It would interpret the ' or " as part of the statement and not part of the data.


    I would like to once again state that you don't want to do this. Espically the part of including address line1,2 with city state zip. It will make it hard to seperate them out correctly - for say a mailing to your consultants. You will have to sperate the Addresses out from them to show correctly in the Address Block.

    S-

Posting Permissions

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