C
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
[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