Splitting right 4 numbers of zipcode into another field

[nemmeh]

Hey everyone, yep I need help again.. haha..

I'm working with a table that contains records for every registered voter. Ok, well it is only current to 1997 and there is an excel update for it, but all the fields are messed up so I'm having to convert and move stuff around to make it all mesh together properly in the end when I actually do the import.

What I need help with is,
In the Voters table, I have a field (Zipcode) which is 9 numbers ex: "704271911" which is basically the first 5 of the zipcode and the last 4. Well the update I have has seperate fields for this. First 5 and Last 4. I need to split it up like this for the original table.

I'm having problems with the syntax.

I need to take the Right([Zipcode],4) and insert those 4 numbers into (Zipcode_L4). I've played with a few different ways of doing it and I just can't seem to get it right, I keep getting errors.

Oh yeh, also after I insert all the correct numbers into the Zipcode_L4 field, could you possibly help me with the delete query to delete those right 4 numbers from the original field (Zipcode).

Can anyone help me with this? Thank you for your assistance as always.

 

[GoDawgs]

I think you're doing this right, not sure what the problem is. Dump all the data into a temp table, then run an append query to move all the data correctly into the final table. The fields for zip would look something like this:

"Zipcode_L4": Right([zipcode],4)
"Zipcode_F5": Left([zipcode],5)

If you want these to be numbers instead of text then make it CInt(Right([zipcode],4)...the same for the other one too. Hope that helps.

Kevin

 

[lespaul]

actually you should keep them as text, you should only store numbers when you plan on doing calculations with them. Since zipcodes aren't usually used in calculations, keep them text.

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!