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?
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?
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
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.
Do you mean columns or rows?
Do you want this: Address1,Address2,Address3
Split into this:
Address1
Address2
Address3
Or This:
Address1 Address2 Address3
Sorry, I mean rows.
I want to go from Address1,Address2,Address3 etc to this
Address1
Address2
Address3
etc
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))
Fab, it only shows the first part of the address though. How do I do it for the other 5 rows please?
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.
Double checked everything but it is definitely only showing the first address line.
in your query try
SplitAddress:replace(Address,",",vbcrlf)
to view more lines, ensure the control is high enough to display multiple lines
Just tried it but got 'Enter Parameter Value? vbcrlf'
OK go back to AccessPowers suggestion
SplitAddress:REPLACE(Address, ",", CHR(13) & CHR(10))
That only brings up the first address line though & I need it for all 6
all sorted, thanks for all your help
Glad you got it sorted. You're welcome.