Combining two fields in a table

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

I have a 5-digit zip field and a 4-digit plus-4 field. I created in my table
a 10 digit newzip field, so that combining these two make a 9-digit zip that
looks like this:
60016-2345. I have no problem creating this in a query, but I want this to
be in a table, and then I will delete the existing zip and the plus field,
then re-name the newzip as zip. How do I do this? Can I convert it from the
query into a table? I know this is basic, but I'm really new to this
 
I have a 5-digit zip field and a 4-digit plus-4 field. I created in my table
a 10 digit newzip field, so that combining these two make a 9-digit zip that
looks like this:
60016-2345. I have no problem creating this in a query, but I want this to
be in a table, and then I will delete the existing zip and the plus field,
then re-name the newzip as zip. How do I do this? Can I convert it from the
query into a table? I know this is basic, but I'm really new to this
Click to expand...

I would suggest opening the table in design view and creating a new 10-byte
text field (ZipNew call it, perhaps).

Run an Update query updating it to

[Zip] & "-" & [Plus4]

If your fields are Number fields rather than Text, use

Format([Zip], "00000") & "-" & Format([Plus4], "0000")

using yor own fieldnames of course.

Check the data, open the table in design view, delete the old zip and plus4
fields and rename Zipnew to Zip; you will probably want to Compact the
database when you're done.

Do you need to allow for non-US postal codes, say from Canada? If so you may
want to check the size; Canadian postcodes are 6 or 7 characters (depending on
if you include the blank), other countries are quite variable.
 
You need an update query, so open in design view and pull down the new field
to the grid.
Click on the Update icon and the rows of the grid change to have one titled
'Update To:' in which you enter --
[Zip] &"-" & [Plus-4]

Run the query.
 
Back
Top