SQL code

[Mirza]

I've created a lookup for city using the Zip code.However,
I get always the Error Msg.Data type mismatch in criteria
expression.I've also tried different criterias like:
" WHERE (zipcode.ZIP)LIKE '" & me!zip & "*') "
" WHERE (zipcode.ZIP)=" & Chr$(34) & Me!ZIp& Chr$(34)
but it won't work.Date type is text.

Thanks

Mirza

Private Sub Zip_AfterUpdate()
Dim strSQL As String
Dim rst As Recordset, dbs As Database
Set dbs = CurrentDb

strSQL = " SELECT * FROM zipcode "
strSQL = strSQL & " WHERE (zipcode.ZIP) = " & Me!Zip

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
Me.City.Value = rst!City

 

[Dirk Goldgar]

I've created a lookup for city using the Zip code.However,
I get always the Error Msg.Data type mismatch in criteria
expression.I've also tried different criterias like:
" WHERE (zipcode.ZIP)LIKE '" & me!zip & "*') "
" WHERE (zipcode.ZIP)=" & Chr$(34) & Me!ZIp& Chr$(34)
but it won't work.Date type is text.

Thanks

Mirza

Private Sub Zip_AfterUpdate()
Dim strSQL As String
Dim rst As Recordset, dbs As Database
Set dbs = CurrentDb

strSQL = " SELECT * FROM zipcode "
strSQL = strSQL & " WHERE (zipcode.ZIP) = " & Me!Zip

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
Me.City.Value = rst!City

If, as you say, ZIP is a text field, then I'd expect these to work

' testing for equality:
... " WHERE (zipcode.ZIP) = '" & Me!Zip & "'"

' testing for initial similarity:
... " WHERE (zipcode.ZIP) Like '" & Me!Zip & "*'"

You had something very similar to these, though, so I'm not sure whether
there's something else going on or not. If these don't work, please
copy and paste exactly what you tried, and exactly what the error
message was each time.

 

[Mirza]

This is the code I tried:

Private Sub Zip_AfterUpdate()
Dim strSQL As String
Dim rst As Recordset, dbs As Database
Set dbs = CurrentDb


strSQL = " SELECT * FROM zipcode "
strSQL = strSQL & " WHERE (zipcode.ZIP) = '" & Me!Zip & "'"

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst

Me.City.Value = rst!City ' Txtfield on Form that should
display the city name



Error Msg I'm getting:

Run-time error '13':

Type mismatch


When I hit debug it goes to this line

Set rst = dbs.OpenRecordset(strSQL)

and rst = Nothing

Thanks


Mirza

 

[Dirk Goldgar]

This is the code I tried:

Private Sub Zip_AfterUpdate()
Dim strSQL As String
Dim rst As Recordset, dbs As Database
Set dbs = CurrentDb


strSQL = " SELECT * FROM zipcode "
strSQL = strSQL & " WHERE (zipcode.ZIP) = '" & Me!Zip & "'"

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst

Me.City.Value = rst!City ' Txtfield on Form that should
display the city name



Error Msg I'm getting:

Run-time error '13':

Type mismatch


When I hit debug it goes to this line

Set rst = dbs.OpenRecordset(strSQL)

and rst = Nothing

Thanks

Aha! You said you were getting "Type mismatch in criteria expression."
This is a different message. My guess is that you're using Access 2000
or later, and it's caused by Access assuming you're defining an ADO
recordset when what you really want is a DAO recordset (the ADO and DAO
libraries both have Recordset objects, but they are incompatible).
Change this:

Dim rst As Recordset, dbs As Database

to this:

Dim rst As DAO.Recordset, dbs As DAO.Database

and let us know if that fixes it. (Technically, you don't need the
"DAO" qualifier for Database, because ADO doesn't define a Database
object, but it never hurts to be on the safe side.)

 

[Guest]

Thank you very much.I have made the change you suggested
and it works great.

Thanks again

Mirza