Working with a String

[Anthony Viscomi]

I have a table(s) that is imported nightly from a Main Frame; within that
table there is a field Addr3 that contains data that looks like the
following:

EL DORADO, AR
PINE BLUFF, AR
MURFREESBORO, TN

Obviously this data represents City & State; my problem is that I am only
concerned with the City portion of this data. Please note there is always a
comma after the City. I would like to via a query or code only view the City
at that point I wxport to query to a csv file. Can somone please help?

Thanks,
Anthony

 

[Brendan Reynolds]

SELECT Left$([CityState],InStr(1,[CityState],",")-1) AS City,
Trim$(Mid$([CityState],InStr(1,[CityState],",")+1)) AS State
FROM tblTest;

See Left Function, InStr Function, Trim Function and Mid Function in the
help files for more info.

 

[Cheryl Fischer]

You can create a string containing only the City by using the Left()
function:

Left([MyField], InStr([MyField], ",") -1)

You can use this expression in a query by simply inserting it into the
Field: row of a query column.

 

[Anthony Viscomi]

Works great! Thanks to all!

You can create a string containing only the City by using the Left()
function:

Left([MyField], InStr([MyField], ",") -1)

You can use this expression in a query by simply inserting it into the
Field: row of a query column.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I have a table(s) that is imported nightly from a Main Frame; within that
table there is a field Addr3 that contains data that looks like the
following:

EL DORADO, AR
PINE BLUFF, AR
MURFREESBORO, TN

Obviously this data represents City & State; my problem is that I am only
concerned with the City portion of this data. Please note there is

always

a
comma after the City. I would like to via a query or code only view the City
at that point I wxport to query to a csv file. Can somone please help?

Thanks,
Anthony