Results 1 to 15 of 15
  1. #1
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60

    Split data into serperate columns

    Hi, I have an address field (eg line1, line 2, line3).

    How can I split this field at each comma so each 'line' has it's own column please?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make the fields in the table,
    make an update query to break it up.
    the 1st query grabs the 1st part..

    Q1 , set the field to update Line1 to: left([addr],instr([addr],",")-1)

    then the next query Q2, remove that part from the address:
    mid([addr],instr([addr],",")+1)

    then run Q1 again but update Line2

  3. #3
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60
    Thanks. Is there a way to split without adding new fields to a table? I need to be able to split into a max of 6 lines to show in a report.

  4. #4
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Do you mean columns or rows?

    Do you want this: Address1,Address2,Address3

    Split into this:
    Address1
    Address2
    Address3

    Or This:
    Address1 Address2 Address3

  5. #5
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60
    Sorry, I mean rows.

    I want to go from Address1,Address2,Address3 etc to this
    Address1
    Address2
    Address3
    etc

  6. #6
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    In that case, you need to set the field type to "Long Text" and run this query:

    Code:
    UPDATE YourTable
    SET Address = REPLACE(Address, ',', CHR(13) & CHR(10))

  7. #7
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60
    Fab, it only shows the first part of the address though. How do I do it for the other 5 rows please?

  8. #8
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    This will do it for all 5 rows. You're going to need to make your textbox bigger to see everything. Or if you're in the table in datasheet view, press your down arrow.

  9. #9
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60
    Double checked everything but it is definitely only showing the first address line.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in your query try

    SplitAddress:replace(Address,",",vbcrlf)

    to view more lines, ensure the control is high enough to display multiple lines

  11. #11
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60
    Just tried it but got 'Enter Parameter Value? vbcrlf'

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK go back to AccessPowers suggestion

    SplitAddress:REPLACE(Address, ",", CHR(13) & CHR(10))

  13. #13
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60
    That only brings up the first address line though & I need it for all 6

  14. #14
    Pure Salt is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    60
    all sorted, thanks for all your help

  15. #15
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Glad you got it sorted. You're welcome.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-03-2015, 11:19 PM
  2. Hiding columns in split form.
    By cementblocks in forum Forms
    Replies: 5
    Last Post: 03-11-2014, 07:16 AM
  3. Replies: 15
    Last Post: 01-12-2011, 05:13 PM
  4. Split string into Date and Time Columns?
    By Hobbes29 in forum Queries
    Replies: 2
    Last Post: 06-08-2010, 06:50 PM
  5. split a column into two seperate columns
    By nybanshee in forum Access
    Replies: 2
    Last Post: 08-14-2008, 04:52 PM

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