Data type mismatch in criteria expression

[Guest]

Hi there!

When I try to run the following code on a button's click event, I get the
above 3634 error:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tblClients where lngDesignerID=
""" & _
Me.lngDesignerID & """", dbOpenSnapshot)
If rs.RecordCount > 0 Then
MsgBox "Sorry, you can't delete this designer as it has already been
used.", _
vbOKOnly + vbInformation, "Sorry"

Exit Sub etc

What's wrong? All help is immensely appreciated.

JR

 

[Brendan Reynolds]

The 'lng' prefix suggests that the field 'lngDesignerID' is probably a
numeric (Long Integer) field, but the quotes around the reference to
'Me.lngDesignerID' would only be appropriate if 'lngDesignerID' was a text
field. If 'lngDesignerID' is, in fact, a numeric field, try it without the
extra quotes, like so ...

Set rs = db.OpenRecordset("select * from tblClients where lngDesignerID
= " & Me.lngDesignerID, dbOpenSnapshot)

 

[Guest]

If the lngDesignerID field is a number type then try this
Set rs = db.OpenRecordset("select * from tblClients where lngDesignerID= " &
Me.lngDesignerID, dbOpenSnapshot)

 

[Guest]

Brendan, Ofer, you guys rock! Thanks, yes I did copy that code from an old
application I did back in the summer and it did originally use a text box.
It's all those quotes! Is there an online source or a good book which will
give me some pointers about doing queries in code? I never know where to put
the quotes or the "&".

Thanks again!

J

 

[Guest]

In the criteria you add single quote for a string field, a # for a date
field, and none for a number.
So, you can have that

"Select * From TableName Where StringField = '" & Param & "'"
Or
"Select * From TableName Where NumberField = " & Param
Or
"Select * From TableName Where DateField = #" & Param & "#"
=============================================
You can also combine the criteria
"Select * From TableName Where StringField = '" & Param & "' And NumberField
= " & Param & " And DateField = #" & Param & "#"
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Guest]

Ahhhh, the clouds part and the sun shines through! Thanks, that really helps!

J