I'm trying to convert a combined City/State field in one table into separate
City and State fields in a destination table using the following method.
Let's say that:
Table1.CityState = "Columbus, OH"
and I want:
Table2.City = "Columbus"
Table2.State = "OH"
so I'm trying:
INSERT INTO Table1 (City, State)
SELECT Left([CityState], (Len([CityState]) - 4)), Right([CityState], 2)
This works for the most part, but fails if the CityState field is less that
four characters. This is from an old database, and some of the records have
unusual data in this field. I would use WHERE Len([CityState]) >=4, but I
need to ...