update querry

[Octet32]

Have a zip code field that has 9 numbes in the field 123459999 there over a
12000 of these is there a way that I can change just the last number which
is
9 to 8 for all the records at one shot?

I tryed putting this into update to zip field But I get a error anyone
know what i am doing wrong

Left(Zip,8) & "8" Where Right(Zip,1) ="9"
i also tryed =Left(Zip,8) & "8" Where Right(Zip,1) ="9";

Thanks
T


Thanks
Tom

 

[Jerry Whittle]

Is the field text or number data type? Hopefully text as the leading zeros
for New England zipcodes would strip off if a number.

If a text field, something like below should work once you put in the right
table and field names.

UPDATE EmpZips
SET EmpZips.[zip] = Left([zip],8) & "8"
WHERE Right([zip],1)=9
AND Len([zip])=9;

Make a backup first just in case something goes wrong!

 

[Octet32]

I was able to run the querry but when it update all the date was gone?

Is the field text or number data type? Hopefully text as the leading zeros
for New England zipcodes would strip off if a number.

If a text field, something like below should work once you put in the right
table and field names.

UPDATE EmpZips
SET EmpZips.[zip] = Left([zip],8) & "8"
WHERE Right([zip],1)=9
AND Len([zip])=9;

Make a backup first just in case something goes wrong!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Have a zip code field that has 9 numbes in the field 123459999 there over a
12000 of these is there a way that I can change just the last number which
is
9 to 8 for all the records at one shot?

I tryed putting this into update to zip field But I get a error anyone
know what i am doing wrong

Left(Zip,8) & "8" Where Right(Zip,1) ="9"
i also tryed =Left(Zip,8) & "8" Where Right(Zip,1) ="9";

Thanks
Tom

 

[Jerry Whittle]

Have you looked at the actual table for the data? An action query, such as
append, update, or delete, won't show the records after it's down. It should
have popped up a message saying how many records that it was going to update
though.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I was able to run the querry but when it update all the date was gone?

Is the field text or number data type? Hopefully text as the leading zeros
for New England zipcodes would strip off if a number.

If a text field, something like below should work once you put in the right
table and field names.

UPDATE EmpZips
SET EmpZips.[zip] = Left([zip],8) & "8"
WHERE Right([zip],1)=9
AND Len([zip])=9;

Make a backup first just in case something goes wrong!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Have a zip code field that has 9 numbes in the field 123459999 there over a
12000 of these is there a way that I can change just the last number which
is
9 to 8 for all the records at one shot?

I tryed putting this into update to zip field But I get a error anyone
know what i am doing wrong

Left(Zip,8) & "8" Where Right(Zip,1) ="9"
i also tryed =Left(Zip,8) & "8" Where Right(Zip,1) ="9";

Thanks
Tom