How to split the columns

[Guest]

I have the following table:

Address
-----------------------------
123, ABC Road, AA City
1, Kings Road, BBB City
23, Queens Street,CCCC Country

Now I would like to split it into 3 columns. However, I
cannot use len, right, left or mid since there is no fix
digit I can count on. What other function in queries I
can use to split this into 3 columns?

Thanks

 

[John Viescas]

StreetNum: Left(Address, Instr(Address, ",") -1)
StreetName: Mid(Address, Instr(Address, ",") + 2, Instr(Instr(Address,
",")+1, Address, ",") - Instr(Address, ",") - 2)
CityCountry: Mid(Address, Instr(Instr(Address, ",") + 1, Address, ",") +2)

If you have Access 2002 or later, you can use InstrRev to simplify the
search for the second comma.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

Thanks, but would you explain how to get the numbers?
How to determine when to -1, +2 etc?

 

[John Viescas]

OK. I'll explain the first one, and you can figure out the rest.

The LEFT function requires two parameters: 1) the string from which you
want "n" Left characters; 2) The length ("n") that you want. The INSTR
function is a bit more complex. The first argument can either be the string
you want to search OR the start position within the string - in which case
the string to search is the second paramter. InStr tells you the position
(relative to 1) within the original string that it found the search string.

In the first case, you want all the characters up to, but not including, the
first comma. So, if Address starts with "12345, First Ave. ...", InStr
should return the value 6 when we look for the first comma. We want only
the first 5 (not the comma), so subtract 1 before giving the result to LEFT.

StreetNum: Left(Address, Instr(Address, ",") -1)


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)