DLookup with two criteria 1

[vba317]

I am trying to develop a Dlookup function that uses two criteria to lookup the value I am not having any luck. The two values are city and state to lookup a zipcode. I have a table called tblZipCode that has columns called City, State and ZipCode. The form is called frmJobInformation. The textboxes on the form are called txtCompanyCity, txtCompanyState and txtCompanyZipCode.

I get a compile error of too many arguments. Any help is appreciated.

Private Sub lstCompanyState_Exit(Cancel As Integer)
Dim X, Y As String
On Error GoTo ERR_CompanyState
Forms![frmJobInformation]![txtCompanyZipCode] = DLookup("[txtCompanyCity]" And "[txtCompanyState]", "[CompanyZipCode]", "tblZipCode", "[txtCompanyZipCode] = Forms![frmJobInformation]!CompanyZipCode")

Exit Sub
ERR_CompanyState:
    MsgBox "No Data"
     Exit Sub

End Sub

 

[MazeWorX]

have you tried

Forms![frmJobInformation]![txtCompanyZipCode] = DLookup("[ZipCode]", "tblZipCode", "[State] = " _
                                              & Forms![frmJobInformation]!txtCompanyState & _
                                              " And " & "[City] = " & Forms![frmJobInformation]!txtCompanyCity)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PHV]

What about this ?

Me!txtCompanyZipCode = DLookup("ZipCode", "tblZipCode", "City='" & Me!txtCompanyCity & "' AND State='" & Me!txtCompanyState & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Sorry I tried both Methods and neither works. I don't get a complile error on either one but after I click on the state I get a no Data message. I checked the table and the City and State that I entered are in the table with the zipcode.

Tom

 

[vba317]

Once again thank you PHV. I looked over your code and realized that I gave you the wrong name, txtCompanyState is lstCompanyState. I changed it and everything works great. Thank you.