Okay, let's assume your table has these fields:
- Address: the existing field containing 1 or 2 lines;
- Address1: a new field where you'll put the first line;
- Address2: a new fields where you'll put the 2nd line.
1. Create a query using this table.
2. Drag [Address] into the grid.
In the Criteria row under this, enter:
Is Not Null
This skips any records that have no address at all.
3. Type This expression into the Field row in the next column:
LinePos: Instr([Address], Chr(13) & Chr(10))
In the Criteria row under this, enter:
0
This gives you only the records that don't have a 2nd line.
4. Change the query into an Update query. (Update on Query menu/ribbon.)
Access adds an Update row to the query design grid.
5. Add the Address1 line to the grid.
In the Update row under this field, enter:
[Address]
6. Run the query. Address1 now contains the same as Address for the records
that don't have a second line.
7. Change the query back to a Select query.
8. Change the criteria under LinePos to:
This gives you the records that do have a second line (ignoring any with
nothing on the 2nd line.)
9. Type into the Field row in a blank column:
L1: Left([Address], Instr([Address], Chr(13) & Chr(10))-1)
10. In the next column, type into the Field row:
L2: Mid([Address], Instr([Address], Chr(13) & Chr(10)) + 2)
11. Test that this last field gives you the right data for the 2 address
lines.
12. Change the query into an Update query again.
13. In the Update row under Address1, enter the expression from step 10
above, without the "L1:", i.e.:
Left([Address], Instr([Address], Chr(13) & Chr(10))-1)
14. In the Update row under Address2, enter the expression from step 11,
without the "L2:".
15. Run the query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Sandspur said:
I have an AddressField that i need to seperate into two fields Address1
and
Address2
the data currently in AddressField has either one or two lines.
Thank you
.
