Question Locked

Movie data from one field to another

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

hi,

i am using microsoft access 2007 and i am trying to move some data from one field to another one. the data look like this "12345-6789" where "12345" is the zip code with a "-" in between and "6789" is the zip+4 code.

what i am trying to do is to move everyting that is after "-". how i could tell access to perform such a task?

thank you in advance.

6 replies

by jck

you can use the LEFT, RIGHT, and MID functions in Access SQL to get the last 4...for example

SELECT RIGHT(, ) AS Plus4 FROM

Hope that helps

by blacknred0

i tried that with the select statement and it worked perfectly. now when i went and did the update apparently it didn't like it. some how said that it was going to update 100's of rows and then when i did a "select statement where is not null" there was "0" rows affected.

here is the example of the statement that i used. please let me know what i am doing wrong.

UPDATE tblName1
SET tblName1.Zip = tblName1.Zip+4
WHERE (tblName1.Zip=RIGHT(tblName1.Zip,4));

could somebody enlighten me in this situation would be much appreciate it.

thanks in advance

by tony hopkinson

Are you saying you have this

Zip Zip+4
12345-6789 NULL

and you want this

Zip Zip+4
12345 6789

?

Update tblName1
Set Zip4 = Right(Zip,4), Zip = Left(Zip,5)
Where Zip4 is null

by blacknred0

yes, this was exactly what i was trying to perform. it works properly. thnx

now i have to make the query better in order not no move other type for zip codes like from Canada and other places. just numbers with dashes now.

thnx

This discussion is closed to new replies.