Zip code linking

[Guest]

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

 

[Stefan Hoffmann]

hi Karl,

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 <--

 

[Guest]

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

 

[Guest]

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