Split Data within a column.

[Guest]

I have a database with 146,000 records. I have a field named Zip_10 (text).
It
contains zipcodes with and without zip+4. the data is entered as follows:

48006
48123-0021
48323-0989
48123
48945-2321
All the records with a zip4 have a hypen. I need to make one column zip
(first 5 digits) and one column zip4 with the digits past the hypen.
I have created a new column called Zip4 but it is empty. I am below NOVICE
level on this stuff. I can do it in excel by using the text to column
command but my full database will not load. I truncates at 65,000 records or
so.

UPDATE MyTable
SET Zip_4 = Right$(Zip_10, Len(Zip_10) - (Instr(Zip_10, "- "),
Zip5 = Left$(Zip_10, Instr(Zip_10, "- ")

Any help? The above doesn't work and I got It off this site. (I change the
names to fit my data).

 

[Guest]

Hi Andrew,

Try setting it equal to Mid(Zip_10,7). Mid will return a zero length string
if the start position is greater than the length of the string, and it will
return all characters to the right of the start point if the length is not
entered (optional third argument, which you could enter as 4 if you want). I
think that should do what you are looking for.

HTH, Ted Allen

 

[Guest]

UPDATE smada001 SET Zip_4 =Mid(Zip_10,7)
Wow, it worked. I spent many hours trying to do it. I have no idea what
"Len" or "InStr" or "Mid" is all about. but I used the the command above and
it worked. Where there was no Zip4 it left the cell blank. Just what I
wanted. I will be using a Geo-coder that will geo-code to the centroid of a
zip4 area. For some reason it can't use the 10 digit string. Thanks alot.
As you can see by the command I was trying to use, I wasn't even close.

 

[Guest]

UPDATE smada001 SET Zip_4 =Mid(Zip_10,7)

Wow, it worked! Thanks.

I tried alot of different combinations of the queries I found on this site
(none worked)
It also left the cell blank if there wasn't data after the hyphen. Just
what I wanted. The Access help program is not very helpful. I couldn't find
what "Len" or "InStru" or "Mid" means or when to use them. Not very good
examples given.

Thanks again. I wasted hours trying to figure it out on my own.

Andy.

 

[Guest]

Hi Andrew,

Glad it worked for you. I agree that Access help can be tricky (to put it
mildly). There is good help available for all of the functions, but you
can't always see it from Access help. Sometimes you have to go to VBA
(Visual Basic) help, which most users aren't even aware exists.

But, one shortcut way to get to the help on functions is to open the
expression builder (such as right clicking in the query design grid where you
would type the field source and choosing build) then go to the list of built
in functions, select one, and click help. That will generally take you to
the help on Access functions.

For whatever reason, Access help displays different subjects in the contents
tab depending on how you get there, and you can't always see the help on
them. I used to not be able to see them, but now I can if I choose
"Programming in Visual Basic" under Access help, then "Visual Basic Language
Reference" and finally "functions".

I guess the main thing is to know that the help is there, you just may have
to look around a little to find it.

By the way, if you want to open visual basic help to see what it has to
offer, the easiest way is probably to type Ctrl-G in the database window,
which will open the VBA environment, and then go to help from there.

HTH, Ted Allen