Zip code linking

K

Karl H

Hi,
I'm linking City & State to a zip code, but I want the function to pay
attention to just the first 5 numbers in the zip code. Please advise how I
would change this code to make that happen.

'Links some zip codes to cities
Private Sub txtZipCode_AfterUpdate()
Dim rsCurr As DAO.Recordset
Dim strSql As String
strSql = "SELECT City, State FROM tblCity WHERE Zip = '" & txtZipCode & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSql)
If rsCurr.EOF = False Then
State = rsCurr!State
txtCity = rsCurr!City
End If
End Sub

Thank you,
Karl
 
S

Stefan Hoffmann

hi Karl,

Karl said:
Hi,
I'm linking City & State to a zip code, but I want the function to pay
attention to just the first 5 numbers in the zip code. Please advise how I
would change this code to make that happen.
strSql = "SELECT City, State FROM tblCity WHERE Zip = '" & txtZipCode & "'"
What about

strSql = "SELECT City, State " & _
"FROM tblCity " & _
"WHERE Left(Zip, 5) = '" & Left(txtZipCode.Value, 5) & "'"
?


mfG
--> stefan <--
 
B

BruceS

Karl,

If you are working with the public (retail, business mail, etc.) be sure to
keep two things in mind:

1) The USPS changes at least a few zip codes every 2 months.
2) Some zips have multiple city names. Many of these are "vanity" names,
and people get very upset when their mail is addressed to "Rockledge, FL
32955" instead of to "Viera, FL 32955".

If you're doing a lot of this, highly recommend that you get the
"city/state" file from one of the USPS approved vendors and use it to update
your lookup table at least once every six months.

Bruce
 
K

Karl H

Thank you, Stefan, that works great!
I very much appreciate your time & expertise,
Karl
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top