Function to Separate City & State

[Guest]

I have a single column with City and State, and also alot of blank fields in
the column. How can I create two separate columns, one for city and one for
state, in a query? The city and state are column delimated. I appreciate
your help, Thanks.

 

[John Spencer]

Assumption:
City and state are COMMA delimited (Oakland, CA)

You can use a calculated field to do it as follows.

Field: CityOnly: Left([CityState] & ",", Instr(1, [CityState],",")-1)

Field: StateOnly: Trim(Mid ([CityState] & ",", Instr(1, [CityState] &
",",",")+1))

 

[Guest]

Thanks that works great. One more thing, I have rows within that column
which are blank and after applying the function I get the #ERROR message in
the blank fields. How should I handle that???

Assumption:
City and state are COMMA delimited (Oakland, CA)

You can use a calculated field to do it as follows.

Field: CityOnly: Left([CityState] & ",", Instr(1, [CityState],",")-1)

Field: StateOnly: Trim(Mid ([CityState] & ",", Instr(1, [CityState] &
",",",")+1))

I have a single column with City and State, and also alot of blank fields
in
the column. How can I create two separate columns, one for city and one
for
state, in a query? The city and state are column delimated. I appreciate
your help, Thanks.

 

[John Spencer]

Interesting, I thought that code would handle that. I see that I missed a
bit on CityOnly. If StateOnly gets errors, then post back and I will try a
different flavor of code.
Field: CityOnly: Left([CityState] & ",", Instr(1, [CityState] & ",",",")-1)

Thanks that works great. One more thing, I have rows within that column
which are blank and after applying the function I get the #ERROR message
in
the blank fields. How should I handle that???

Assumption:
City and state are COMMA delimited (Oakland, CA)

You can use a calculated field to do it as follows.

Field: CityOnly: Left([CityState] & ",", Instr(1, [CityState],",")-1)

Field: StateOnly: Trim(Mid ([CityState] & ",", Instr(1, [CityState] &
",",",")+1))

I have a single column with City and State, and also alot of blank
fields
in
the column. How can I create two separate columns, one for city and
one
for
state, in a query? The city and state are column delimated. I
appreciate
your help, Thanks.

 

[MGFoster]

I have a single column with City and State, and also alot of blank fields in
the column. How can I create two separate columns, one for city and one for
state, in a query? The city and state are column delimated. I appreciate
your help, Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you meant comma-delimited instead of "column delimated" [sic] you can
get the separate items like this:

column value = "Oakland, CA"

City:
Left(column_name,InStr(column_name, ",")-1)

Yields "Oakland"

State:
Trim(Right(column_name, Len(column_name)-InStr(column_name, ",")))

Yields "CA"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+5FR4echKqOuFEgEQIqWwCgz8S5aLG8XPcPQrKfRX/wZEPo9a8AoKik
eL6ScxaTVWM3icv1EhYLPDiI
=Z+Cr
-----END PGP SIGNATURE-----