If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Split a string in one field into two fields
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 |
#2
|
|||
|
|||
Split a string in one field into two fields
Caleb
See comments in-line below... "Caleb" wrote in message ... 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 |
#3
|
|||
|
|||
Split a string in one field into two fields
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. "Jeff Boyce" wrote: Caleb See comments in-line below... "Caleb" wrote in message ... 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 |
#4
|
|||
|
|||
Split a string in one field into two fields
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 "fredrickson" wrote in message ... 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. "Jeff Boyce" wrote: Caleb See comments in-line below... "Caleb" wrote in message ... 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 |
#5
|
|||
|
|||
Split a string in one field into two fields
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 Caleb wrote: 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 |
Thread Tools | |
Display Modes | |
|
|