I created a field years ago which contains City and State together, i.e,
Dallas, TX. (okay, bad move, I know)... But now with thousands of records I
want to seperate this information correctly by having one field for city and
another for state.

I will smply rename the "city state" field to "CITY" and create a new field
for "ST." Easy enough. The question is how to populate the new field with
the new data without physically typing in "CTRL and ("), record after record.


Earlier posts don't seem to answer this. Help & thanks.

For this to work, each record much have a comma ( , ) in your city state
field. Fix any records without one. Also make a backup of the table or,
better yet, entire database, first.

Run something like the following in a query. Make sure that you are using
the proper field names. If it returns what you want, you can then use them in
an update query once you add the State field.

Cities: Left([CityState],InStr(1,[CityState],",")-1)

States: Trim(Mid([CityState],InStr(1,[CityState],",")+1))
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Phil C" wrote:

> I created a field years ago which contains City and State together, i.e,
> Dallas, TX. (okay, bad move, I know)... But now with thousands of records I
> want to seperate this information correctly by having one field for city and
> another for state.
>
> I will smply rename the "city state" field to "CITY" and create a new field
> for "ST." Easy enough. The question is how to populate the new field with
> the new data without physically typing in "CTRL and ("), record after record.
>
> Earlier posts don't seem to answer this. Help & thanks.

Sorry, amateur here. Let's see if I have it:
New Query - Design
Choose the table
Column one using citystate enter your string below "Cities"
Then Column two using new ST field enter your the other string "States"
When I try this get an Invalid Procedure Call

"Jerry Whittle" wrote:

> For this to work, each record much have a comma ( , ) in your city state
> field. Fix any records without one. Also make a backup of the table or,
> better yet, entire database, first.
>
> Run something like the following in a query. Make sure that you are using
> the proper field names. If it returns what you want, you can then use them in
> an update query once you add the State field.
>
> Cities: Left([CityState],InStr(1,[CityState],",")-1)
>
> States: Trim(Mid([CityState],InStr(1,[CityState],",")+1))
> --
> Jerry Whittle
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> "Phil C" wrote:
>
> > I created a field years ago which contains City and State together, i.e,
> > Dallas, TX. (okay, bad move, I know)... But now with thousands of records I
> > want to seperate this information correctly by having one field for city and
> > another for state.
> >
> > I will smply rename the "city state" field to "CITY" and create a new field
> > for "ST." Easy enough. The question is how to populate the new field with
> > the new data without physically typing in "CTRL and ("), record after record.
> >
> > Earlier posts don't seem to answer this. Help & thanks.

Hi,

Make double sure that the table's column is spelt CityState. If not, such as
looking like City State with a space, change where I put CityState to match
in all 4 places. I didn't put in the space in my examples.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Phil C" wrote:

> Sorry, amateur here. Let's see if I have it:
> New Query - Design
> Choose the table
> Column one using citystate enter your string below "Cities"
> Then Column two using new ST field enter your the other string "States"
> When I try this get an Invalid Procedure Call
>
> "Jerry Whittle" wrote:
>
> > For this to work, each record much have a comma ( , ) in your city state
> > field. Fix any records without one. Also make a backup of the table or,
> > better yet, entire database, first.
> >
> > Run something like the following in a query. Make sure that you are using
> > the proper field names. If it returns what you want, you can then use them in
> > an update query once you add the State field.
> >
> > Cities: Left([CityState],InStr(1,[CityState],",")-1)
> >
> > States: Trim(Mid([CityState],InStr(1,[CityState],",")+1))
> > --
> > Jerry Whittle
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> > "Phil C" wrote:
> >
> > > I created a field years ago which contains City and State together, i.e,
> > > Dallas, TX. (okay, bad move, I know)... But now with thousands of records I
> > > want to seperate this information correctly by having one field for city and
> > > another for state.
> > >
> > > I will smply rename the "city state" field to "CITY" and create a new field
> > > for "ST." Easy enough. The question is how to populate the new field with
> > > the new data without physically typing in "CTRL and ("), record after record.
> > >
> > > Earlier posts don't seem to answer this. Help & thanks.