Strip Data & Insert Into New Field

[Guest]

My table has a field called [City]. Data in this field has a City Name and a
State designator.

IE Boston, MA

I have created a new field in the table named [State]

I would like to insert the state deisgnator into the new field and then
delete everything after the comma in the field [City]

I would imagine that this should be a 2 step process

1st process
Move the state designator over (it appears that there is a null between the
, and the state designator MA) I need help with the syntax to accomplish
this.

If the 1st process works OK.

2nd process
Remove all characters after the , in the field [City]
Again I could use help with the syntax to get this accomplished.
(I would imagine this would be an update query but not positive)

Will I have to run a 3rd process to remove the nulls that may be inserted
into the State field when I run the 1st process? If so then I would imagine
that you already know that I would need help with that syntax also.

Thanks in advance for any insight.

 

[fredg]

My table has a field called [City]. Data in this field has a City Name and a
State designator.

IE Boston, MA

I have created a new field in the table named [State]

I would like to insert the state deisgnator into the new field and then
delete everything after the comma in the field [City]

I would imagine that this should be a 2 step process

1st process
Move the state designator over (it appears that there is a null between the
, and the state designator MA) I need help with the syntax to accomplish
this.

If the 1st process works OK.

2nd process
Remove all characters after the , in the field [City]
Again I could use help with the syntax to get this accomplished.
(I would imagine this would be an update query but not positive)

Will I have to run a 3rd process to remove the nulls that may be inserted
into the State field when I run the 1st process? If so then I would imagine
that you already know that I would need help with that syntax also.

Thanks in advance for any insight.

If all the records are in exactly City, State (2 character State
designation) format, you can use in an Update Query:

Update YourTable Set YourTable.[State] = Right([City],2),
YourTable.[NewCityField]=Left([City],InStr([City],",")-1);

If the City field does not have a comma or the State can be greater
than 2 characters, post back.

 

[Guest]

Worked Great! Thanks so much for your help. I now have a very usable table.

My table has a field called [City]. Data in this field has a City Name and a
State designator.

IE Boston, MA

I have created a new field in the table named [State]

I would like to insert the state deisgnator into the new field and then
delete everything after the comma in the field [City]

I would imagine that this should be a 2 step process

1st process
Move the state designator over (it appears that there is a null between the
, and the state designator MA) I need help with the syntax to accomplish
this.

If the 1st process works OK.

2nd process
Remove all characters after the , in the field [City]
Again I could use help with the syntax to get this accomplished.
(I would imagine this would be an update query but not positive)

Will I have to run a 3rd process to remove the nulls that may be inserted
into the State field when I run the 1st process? If so then I would imagine
that you already know that I would need help with that syntax also.

Thanks in advance for any insight.

If all the records are in exactly City, State (2 character State
designation) format, you can use in an Update Query:

Update YourTable Set YourTable.[State] = Right([City],2),
YourTable.[NewCityField]=Left([City],InStr([City],",")-1);

If the City field does not have a comma or the State can be greater
than 2 characters, post back.