Matt,
To begin with, add the field in your table design. Then start making a
new query in design view, add the table and drag and drop the zip field
down to the grid. Go to menu Query > Update; this will change your
simple select query to an Update query: notice the new line thta's been
added to the grid, headed "Update To". In that line under the zip field
type:
Right([field1],5)
where I have assumed that the original field was called field1; shange
to the actual field name. When you run this query, the xip field will be
populated with the zip code from the original field (assuming it is
always in the last rightmost positions in the original field!).
This done, you can eliminate the zip code from the original field by
doing an update on the original field with the expression:
Left([field1], Len([field1])-6)
which assumes there is alwasya space and a five-character zip at the end
of the field.
Before you try anything, make sure you make a backup copy of the
original table!
HTH,
Nikos
