Split a string in one field into two fields

[Caleb]

Okay so I have a customer database, three of the fields are for Zip codes,
[Zip] [Zip4] & [zfmt] here is an example of what should be in each field for
a record:
[Zip] [Zip4] [zfmt]
12345 6789 12345-6789
However they are not all that way, in many records the [Zip] field contains
what should be in the [zfmt] field (12345-6789 instead of just 12345) and the
[Zip4] & [zfmt] fields are blank for these records. Now I know you can fix
this in Excell with the concatenate and left and right text formulas so I'm
sure there has to be a way to do it in access.

My second problem is that for some reason most the Zip codes that started
with a 0 had the 0 truncated for some reason, leaving me with a handfull of 4
digit zip codes that need a 0 readded to the beginning. I was sort of able to
remedy this by running a query with (Like "????") as the Zip criteria and
made a new field (ZipNEW: "0" & [Zip]) however I only know how to make this
fix permanent by making a new table with the query, but this new table only
has the records I fixed (the records that are (Like "????")), and the
original truncated zip records are still in the Master.

This has to be simpler than I'm making it because its pretty easy to fix in
Excell but if I can avoid all the exporting and such with a query that would
be great. Any help would be appreciated! If you have any questions or need me
to clarify something please just ask. Thanks in advance!
Caleb

 

[Jeff Boyce]

Caleb

See comments in-line below...

Okay so I have a customer database, three of the fields are for Zip codes,
[Zip] [Zip4] & [zfmt] here is an example of what should be in each field
for
a record:
[Zip] [Zip4] [zfmt]
12345 6789 12345-6789

Why use three when one would do? If you store zip (and sometimes z+4) in a
single field, you can use a query to get the other parts.

However they are not all that way, in many records the [Zip] field
contains
what should be in the [zfmt] field (12345-6789 instead of just 12345) and
the
[Zip4] & [zfmt] fields are blank for these records. Now I know you can fix
this in Excell with the concatenate and left and right text formulas so
I'm
sure there has to be a way to do it in access.

.... and this is why you only use one field ... users don't always do what
you KNOW is the right way...

My second problem is that for some reason most the Zip codes that started
with a 0 had the 0 truncated for some reason, leaving me with a handfull
of 4
digit zip codes that need a 0 readded to the beginning. I was sort of able
to
remedy this by running a query with (Like "????") as the Zip criteria and
made a new field (ZipNEW: "0" & [Zip]) however I only know how to make
this
fix permanent by making a new table with the query, but this new table
only
has the records I fixed (the records that are (Like "????")), and the
original truncated zip records are still in the Master.

I'm going to take a wild guess that your underlying field's data type is
numeric. The following values: 000001, 0001, 01 are all the same as 1.
(US) Zip codes may LOOK like numbers, but they aren't -- they're codes. And
you won't be doing any math on them, so don't store them as numbers ...
change the data type to text. You will have to do cleanup on those that
'lost' their leading 00's.

This has to be simpler than I'm making it because its pretty easy to fix
in
Excell but if I can avoid all the exporting and such with a query that
would
be great. Any help would be appreciated! If you have any questions or need
me
to clarify something please just ask. Thanks in advance!
Caleb

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[fredrickson]

Three fields are used because thats how the data is given to me, Im currently
working on a project that requires me to merge multiple lists together, its
too complicated for me to go to much into it I just need help figuring out
how to run a query that will fix the current data, its not a design issue, we
dont have users entering this data, its the way we get the lists.

Also I wasn't looking for help understanding why the zeros were truncated, I
can understand that on my own, but changing the data type doesn't bring the
zeros back, I need help getting those zeros back, I mentioned earlier that I
made a query that did it but I dont know how to get that information back
into the master list without causing duplicated data.

Caleb

See comments in-line below...

Okay so I have a customer database, three of the fields are for Zip codes,
[Zip] [Zip4] & [zfmt] here is an example of what should be in each field
for
a record:
[Zip] [Zip4] [zfmt]
12345 6789 12345-6789

Why use three when one would do? If you store zip (and sometimes z+4) in a
single field, you can use a query to get the other parts.

However they are not all that way, in many records the [Zip] field
contains
what should be in the [zfmt] field (12345-6789 instead of just 12345) and
the
[Zip4] & [zfmt] fields are blank for these records. Now I know you can fix
this in Excell with the concatenate and left and right text formulas so
I'm
sure there has to be a way to do it in access.

.... and this is why you only use one field ... users don't always do what
you KNOW is the right way...

My second problem is that for some reason most the Zip codes that started
with a 0 had the 0 truncated for some reason, leaving me with a handfull
of 4
digit zip codes that need a 0 readded to the beginning. I was sort of able
to
remedy this by running a query with (Like "????") as the Zip criteria and
made a new field (ZipNEW: "0" & [Zip]) however I only know how to make
this
fix permanent by making a new table with the query, but this new table
only
has the records I fixed (the records that are (Like "????")), and the
original truncated zip records are still in the Master.

I'm going to take a wild guess that your underlying field's data type is
numeric. The following values: 000001, 0001, 01 are all the same as 1.
(US) Zip codes may LOOK like numbers, but they aren't -- they're codes. And
you won't be doing any math on them, so don't store them as numbers ...
change the data type to text. You will have to do cleanup on those that
'lost' their leading 00's.

This has to be simpler than I'm making it because its pretty easy to fix
in
Excell but if I can avoid all the exporting and such with a query that
would
be great. Any help would be appreciated! If you have any questions or need
me
to clarify something please just ask. Thanks in advance!
Caleb

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Take a look at formatting the numeric field as "00000" to get the zeros
back.

Consider using nested IIF() statements to put all the various combinations
back together again.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Three fields are used because thats how the data is given to me, Im
currently
working on a project that requires me to merge multiple lists together,
its
too complicated for me to go to much into it I just need help figuring out
how to run a query that will fix the current data, its not a design issue,
we
dont have users entering this data, its the way we get the lists.

Also I wasn't looking for help understanding why the zeros were truncated,
I
can understand that on my own, but changing the data type doesn't bring
the
zeros back, I need help getting those zeros back, I mentioned earlier that
I
made a query that did it but I dont know how to get that information back
into the master list without causing duplicated data.

Caleb

See comments in-line below...

Okay so I have a customer database, three of the fields are for Zip
codes,
[Zip] [Zip4] & [zfmt] here is an example of what should be in each
field
for
a record:
[Zip] [Zip4] [zfmt]
12345 6789 12345-6789

Why use three when one would do? If you store zip (and sometimes z+4) in
a
single field, you can use a query to get the other parts.

However they are not all that way, in many records the [Zip] field
contains
what should be in the [zfmt] field (12345-6789 instead of just 12345)
and
the
[Zip4] & [zfmt] fields are blank for these records. Now I know you can
fix
this in Excell with the concatenate and left and right text formulas so
I'm
sure there has to be a way to do it in access.

.... and this is why you only use one field ... users don't always do
what
you KNOW is the right way...

My second problem is that for some reason most the Zip codes that
started
with a 0 had the 0 truncated for some reason, leaving me with a
handfull
of 4
digit zip codes that need a 0 readded to the beginning. I was sort of
able
to
remedy this by running a query with (Like "????") as the Zip criteria
and
made a new field (ZipNEW: "0" & [Zip]) however I only know how to make
this
fix permanent by making a new table with the query, but this new table
only
has the records I fixed (the records that are (Like "????")), and the
original truncated zip records are still in the Master.

I'm going to take a wild guess that your underlying field's data type is
numeric. The following values: 000001, 0001, 01 are all the same as 1.
(US) Zip codes may LOOK like numbers, but they aren't -- they're codes.
And
you won't be doing any math on them, so don't store them as numbers ...
change the data type to text. You will have to do cleanup on those that
'lost' their leading 00's.

This has to be simpler than I'm making it because its pretty easy to
fix
in
Excell but if I can avoid all the exporting and such with a query that
would
be great. Any help would be appreciated! If you have any questions or
need
me
to clarify something please just ask. Thanks in advance!
Caleb

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

You can try a series of update queries to fix the data in the table

BACKUP your database BEFORE you try this.
BACKUP your database BEFORE you try this.

Update [YourTable]
SET [Zip] = Right("0000" & [Zip])
WHERE Len([zip])<5 AND [Zip] is not Null

UPDATE [YourTable]
SET Zip4 = Right([Zip],4)
WHERE Zip4 is Null and Zip like "#####[-]####"

UPDATE [YourTable]
SET Zfmt = [Zip]
WHERE Zfmt is Null and Zip like "#####[-]####"

UPDATE [YourTable]
SET Zfmt = [Zip] & "-" & [Zip4]
WHERE Zfmt is Null and Zip like "#####" and Zip4 Like "####"

Finally
UPDATE [YourTable]
SET ZIP= Left([Zip],5)
WHERE Len([ZIP])>5


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County