How do you create an update query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've looked through the knowledge base and I have not found something
specific to building an update query. I'm building an update query to update
between fields in the same table. Unfortunately, the text command I used is
not working.

How do I enter this command in the Update to field: Right$("00000" &
OldZipField,5) using the following fields: postal_code and zip

This was part of my greater question of fix postal codes, which I received
the below solution:

When you imported, Access must have set the field to numeric.

Add a new Zipcode field to your table, and make it Text.

Use an update query to update the new field to Right$("00000" & OldZipField,
5)

Once you've checked that the new field is correct, you can delete the old
field.
 
You must use square brackets in the Update To field. Asumming Postal_Code
is the number field and Zip is the new field

Field: ZIP
UpDate to: Format([Postal_Code],"00000")

Field: Postal_Code
Update To: <<Blank>>
Where: Like "????" or Like "???"

That should update only those ZIP fields that are three or four characters
in length to five characters (adding a leading zero or leading zeroes).

SQL statement would look like

UPDATE YourTable
SET Zip = Format([Postal_Code],"00000")
WHERE Postal_Code like Like "????" or Postal_Code Like "???"

It would be a good idea to make a backup of the data before you do this. As
bulk changes are not undoable.
 
Back
Top