why is this updating all rows?

[Guest]

trying to split zip code into zip and zip 4, but not all records have +4 so I
added a where clause, but I still get warned that all my rows will be
updated. What's wrong?

UPDATE [Copy of MEMBER] SET [Copy of MEMBER].ZipCode = Left([ZipCode],5),
[Copy of MEMBER].Zip4 = Right([ZipCode],4)
WHERE ((Len("ZipCode")>"6"));

 

[John Spencer (MVP)]

You need some small changes. Your code is testing the len of the word
"ZIPCODE", when you really want to test the length of the value. So replace the
quote marks with brackets []. Also, remove the quotes around "6", you should be
testing for a number value not a text value. Lastly, I changed the Right to Mid
on the assignment to zip4. this would take care of the condition where the zip
code was improperly entered as 6,7,8, or 9 digits. It would only get the
characters after the first 5.

UPDATE [Copy of MEMBER] SET [Copy of MEMBER].ZipCode = Left([ZipCode],5),
[Copy of MEMBER].Zip4 = Mid([ZipCode],6)
WHERE ((Len([ZipCode])>5));