creating a new field from part of another

[Jerry Crosby]

I have a field with City, State and Zip all together (didn't create it that
way, I inherited it!). I want to create a new field in the table with just
the zip code, so I can sort it by zip for mailing purposes.

I assume it would be a "create a new table" query, but how do I code it?

Thanks in advance.

Jerry

 

[Ken Snell [MVP]]

You can actually use an update query. Just create a new field in the same
table for the zip code value (name it ZipCode).

Assuming that the zip code consists of all characters in the field after the
last space character:

UPDATE TableName
SET ZipCode = Mid([CityStateZipField], InStrRev([CityStateZipField], " ") +
1);

 

[Jerry Crosby]

Thanks! Perfect! Did the trick!

Jerry