parsing query

[Lori]

I have am importing data into Access and I need to cleanup one of the fields.
The City field contains City Name, State and Zip in one field. I only want
to keep the City Name - it is delimited with a comma. How can I parse the
field to keep everything to the left of the comma?

Thanks!

 

[Ofer]

Try

City: Trim(Left([FieldName],Instr([FieldName],",")-1))

 

[Tom Ellison]

Dear :Lori:

I recommend you try this:

Left("City, State 00111", InStr("City, State 00111", ",") - 1)

Replace my quoted string with the column name in your table.

The above returns "City" in my test.

Please let me know if this helped.

Tom Ellison

 

[fredg]

I have am importing data into Access and I need to cleanup one of the fields.
The City field contains City Name, State and Zip in one field. I only want
to keep the City Name - it is delimited with a comma. How can I parse the
field to keep everything to the left of the comma?

Thanks!

CityOnly:Left([CombinedField],InStr([CombinedField],",")-1)

 

[Lori]

Hi Tom,

Here's what I tried: Left("P_City",InStr("P_City",",")-1)

P_City is my field name - I get an error stating invalid procedure call.
What am I doing wrong? Thanks!

 

[Tom Ellison]

Dear Lori:

First, when it is not a string literal (as in my test) do not put the value
(your column name) inside quotes.

Left(P_City, InStr(P_City, ",") - 1)

If any value in the column is missing the comma you will have a problem.
You may need to test for that if it comes up. If it does, what would you
want to do?

Tom Ellison