Prev: Forms Drop down list
Next: Forms Option Group
From: Barry on 18 Feb 2010 20:28
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
--
Barry
From: John W. Vinson on 18 Feb 2010 22:24
On Thu, 18 Feb 2010 17:28:01 -0800, Barry <barry(a)discussions.microsoft.com>
wrote:

>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 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.

--

John W. Vinson [MVP]
From: KARL DEWEY on 18 Feb 2010 23:01
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.

--
Build a little, test a little.


"Barry" wrote:

> 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
> --
> Barry
 | 
Pages: 1
Prev: Forms Drop down list
Next: Forms Option Group