Query Criteria Help

[Access_Headaches]

We have a mailing list with 83,000 names in it. Our problem is we need to send it to our printers without Full zip codes "55555-5555".

I ran a query to find the incorrect format entries. "*****-****" and came back with 43,256.

Now I need to do an update query to change them to a five digit zip code. Can someone let me know how to do this?

I tried

Update to "*****"
Criteria "*****-****"

That did not work. Any help would be fantastic! Thanks.

 

[dkinley]

I wouldn't jack with the original fields but use a variant to retrieve what you want.

For instance, in the QBE go to the last column and put in something like ....

ShortZip: Left([ZipCodeFieldName], 5)

Change the ZipCodeFieldName to whatever the field name you have that holds the zip codes in question. Then use the newly created variable as the field "ShortZip" for printing purposes.

In this manner, the original data is left whole for later clean-up, etc.

HTH,
-dK

 

[Access_Headaches]

I tried what you said and it worked but its just an illusion (As you stated). I was hoping to "Clean up" the current data as well though, with a simple update query.

Thanks Again.

 

[vbaInet]

I tried what you said and it worked but its just an illusion (As you stated).

dk does wonders

 

[dkinley]

Hehe .. I like her sister, too.

I wasn't sure if you (or someone) wanted the full zip (in the future) and was going to actually fix them or not.

If you want to clean it up permanently, use the same syntax in the "Update" row on an update query.

-dK

 

[Access_Headaches]

I am extremely new to this. What is the exact code I enter in "Update to" ?

The comma keeps coming back with errors and if I enter " " around the code its turns it into text to fill the box in with my coding.

 

[dkinley]

It seems you are just entering a field name. Without brackets it will auto-input quotes - but for what you want, this probably isn't correct.

1. Back up your table.
2. Create a new query.
3. Select Update query.
4. Drop the zip code field into a column.
5. In the update row in this column input: Left([ZipCodeFieldName], 5)
6. Run the query.
7. Check the results.

Note: On (5) no quotes should be anything and replace [ZipCodeFieldName] with the field name from (4) and leave the brackets.

-dK

 

[Access_Headaches]

Brilliant !!! Thanks again!

 

[Access_Headaches]

If I may borrow your knowledge for one more thing. I would like to set up a "Before Update Rule" in the "Zip" field so that people cannot enter the full zip code anymore. The problem is We do have Canada addresses as well so simply only allowing five digits in the field wont work.

My question is what do you suggest and how should I write the rule in the before update area?

Thanks a million again, the basics only get you so far in access.

 

[dkinley]

Okay ... suppose you have a country control on the form. Suppose for data integrity purposes it was a combo box control (and control will work, just using this as an example). Also, suppose the combo box stored the country primary key (autonumber) in the first column so the rowsource of the combo box had two columns (ID, CountryName).

In the forms event, something like ...

If Me.cboCountry.Column(1) = "United States" Then
   If Len(Nz(Me.txtZip, "")) <> 5 Then
      MsgBox "Please enter zip correctly for the US.", , "Zip"
      Me.txtZip.SetFocus
      Cancel = True
   End If
ElseIf Me.cboCountry.Column(1) = "Canada" Then
   If Len(Nz(Me.txtZip, "")) <> ??? Then
      MsgBox "Please enter zip correctly for Canada.", , "Zip"
      Me.txtZip.SetFocus
      Cancel = True
   End If
End If

If doing more than two or something, might consider a Select Case. This is all air code but should get you started.

HTH,
-dK

 

[Access_Headaches]

Amazing, this is exactly what I was looking for! Thank you to no end once again, you've saved me from an expensive bar tab tonight!

 

[dkinley]

You betcha. Glad I could contribute (effectively).

Good luck!

-dK