zip + 4 in one field to two

[Guest]

Old table has zip + 4 in one field, I want to split it into two fields. How
do I read each part to update the new table.

btw, the data has already been copied, so if there is a way just to read the
field on current table, chop off the 4 and add that part it to the new field
that would be great too.

tia.

HB.

 

[Rick B]

Left([ZipField],5)
and
Right([ZipField],4)

 

[fredg]

Old table has zip + 4 in one field, I want to split it into two fields. How
do I read each part to update the new table.

btw, the data has already been copied, so if there is a way just to read the
field on current table, chop off the 4 and add that part it to the new field
that would be great too.

tia.

HB.

Back up your table first.
Add a Last4 and a First5 (Text datatypes) fields to your table.
If EVERY Zip has the 9 characters, then you can use an Update query to
separate the last 4 digits from the first 5.
Update YourTable Set YourTable.Last4 = Right([OldZip],4),[First5] =
Left([OldZip],5);

The above retains the original OldZip field.

If you wish to permanently change the old zip field change the last
part of the SQL to:
[OldZip] = Left([OldZip],5);

 

[Guest]

thank you both...!!!

Old table has zip + 4 in one field, I want to split it into two fields. How
do I read each part to update the new table.

btw, the data has already been copied, so if there is a way just to read the
field on current table, chop off the 4 and add that part it to the new field
that would be great too.

tia.

HB.

Back up your table first.
Add a Last4 and a First5 (Text datatypes) fields to your table.
If EVERY Zip has the 9 characters, then you can use an Update query to
separate the last 4 digits from the first 5.
Update YourTable Set YourTable.Last4 = Right([OldZip],4),[First5] =
Left([OldZip],5);

The above retains the original OldZip field.

If you wish to permanently change the old zip field change the last
part of the SQL to:
[OldZip] = Left([OldZip],5);

 

[Guest]

Unfortunately, they don't all have the +4 i guess I'll have to add a where
Length(field)>5 ??

Old table has zip + 4 in one field, I want to split it into two fields. How
do I read each part to update the new table.

btw, the data has already been copied, so if there is a way just to read the
field on current table, chop off the 4 and add that part it to the new field
that would be great too.

tia.

HB.

Back up your table first.
Add a Last4 and a First5 (Text datatypes) fields to your table.
If EVERY Zip has the 9 characters, then you can use an Update query to
separate the last 4 digits from the first 5.
Update YourTable Set YourTable.Last4 = Right([OldZip],4),[First5] =
Left([OldZip],5);

The above retains the original OldZip field.

If you wish to permanently change the old zip field change the last
part of the SQL to:
[OldZip] = Left([OldZip],5);

 

[Jeff Boyce]

Why?

As in, why do you feel the need to split them? What business need are you
trying to solve by doing so?

 

[Guest]

We need a zip code count report showing number of households in each zip.
When the zip+four was in one field, it was counted as a separate zip. Instead
of getting 2 hits at 11345 we got one at 11345 and one at 11345-2356. I
didn't know or think it was possible to query it as a group, or, on the
report group them all together with a count. I thought splitting them would
be the easiest way to go.

Is there a way to query to group all the 11345s together? Or, on a report,
group them? Not that it matters now, but for my own learning I'd like know
if there was a way to do this w/out splitting them.

thanks.

HB

 

[fredg]

We need a zip code count report showing number of households in each zip.
When the zip+four was in one field, it was counted as a separate zip. Instead
of getting 2 hits at 11345 we got one at 11345 and one at 11345-2356. I
didn't know or think it was possible to query it as a group, or, on the
report group them all together with a count. I thought splitting them would
be the easiest way to go.

Is there a way to query to group all the 11345s together? Or, on a report,
group them? Not that it matters now, but for my own learning I'd like know
if there was a way to do this w/out splitting them.

thanks.

HB

You could have counted Left([Zip+4],5)

 

[Guest]

Thanks, now I know about the left and right functions. However, I already
split the fields.

We need a zip code count report showing number of households in each zip.
When the zip+four was in one field, it was counted as a separate zip. Instead
of getting 2 hits at 11345 we got one at 11345 and one at 11345-2356. I
didn't know or think it was possible to query it as a group, or, on the
report group them all together with a count. I thought splitting them would
be the easiest way to go.

Is there a way to query to group all the 11345s together? Or, on a report,
group them? Not that it matters now, but for my own learning I'd like know
if there was a way to do this w/out splitting them.

thanks.

HB

You could have counted Left([Zip+4],5)