Run-time error '3464': Data type mismatch in criteria expression!

[Kita]

hello guys

We're making a database in access 2002 and we keep getting this error...

here's the code:

Dim Base As Database
Dim str, strTest0 As String
Dim strTest1, strTest2, strTest3 As Double
Dim rcd, rcd1, rcd2 As Recordset
Set Base = CurrentDb
Set rcd = Base.OpenRecordset("SLProd_Out", dbOpenDynaset)
Set rcd1 = Base.OpenRecordset("SLProd_In", dbOpenDynaset)
strTest0 = [Forms]![Shelflife]![Lb_Armazem].Column(0)
strTest1 = [Forms]![Shelflife]![Lb_Armazem].Column(1)
strTest2 = [Forms]![Shelflife]![Lb_Armazem].Column(2)
strTest3 = [Forms]![Shelflife]![Lb_UtiOfi].Column(4)
str = "SELECT SLProd_In.PN, SLProd_In.TR, SLProd_In.Dt_L, SLProd_Out.TAP_Num" _
& " FROM SLProd_Out,SLProd_In WHERE (((SLProd_In.PN)='" & strTest0 & "') AND ((SLProd_In.TR)=" & strTest1 & ") AND ((SLProd_In.Dt_L)=" & strTest2 & ") AND ((SLProd_Out.TAP_Num)=" & strTest3 & "))"
Set rcd2 = Base.OpenRecordset(str)

when we click Debug it goes directly to the line "Set rcd2 = Base.OpenRecordset(str)"

hope u guys can help me
Thanks in advance

 

[KenHigg]

I would suggest you examine str in the imediate window at the point that it errors. I'm guessing something is wrong with the sql statement

 

[KenHigg]

(Looks like you need some more quotes:

((SLProd_In.Dt_L)=" & strTest2 & ")

Should be be:

((SLProd_In.Dt_L)='" & strTest2 & "')

???

 

[Kita]

Thanks alot im going to try it

Update#1: Didnt work :/ gonna try the immediate thingy tho
Update#2: Can't see anything wrong with the sql code neither

 

[gemma-the-husky]

you need to check the syntax of the string carefully - display the sql string in a msgbox before using it

you are obviously aware that strings/numbers/dates need to be masked differently, but you also need to allow for null values.


Actually, the error sounds like you have a string/numeric mismatch somewhere

 

[Kita]

The error continues, so i created the msbox, like u told me, and this is what it appeared...

Thanks in Advance

 

[georgedwilkinson]

It looks like you have numeric values in quotes. That will give you a type mismatch.

Copy your sql from the immediate window (using Ken's advice above) into a query SQL window and try it out. Remove the ' from the numeric values and try it out. Then apply what you learn in the query SQL window to your code.

 

[KenHigg]

Here's a debugging tip: Romove stuff until it works and then you'll have a starting point for finding what is causing the error. In this cas remove all of the criteria and see if it will return all of the records. Then add one back in at a time.

Otherwise, only the 'text' data fields require the single quotes. Not sure what you data types are for SLProd_In.TR, SLProd_In.Dt_L or SLProd_Out.TAP_Num. - ?

 

[DJkarl]

hello guys

We're making a database in access 2002 and we keep getting this error...

here's the code:

Dim Base As Database
[COLOR=red]Dim str,[/COLOR] strTest0 As String
Dim strTest1, strTest2, strTest3 As Double
Dim rcd, rcd1, rcd2 As Recordset
Set Base = CurrentDb
Set rcd = Base.OpenRecordset("SLProd_Out", dbOpenDynaset)
Set rcd1 = Base.OpenRecordset("SLProd_In", dbOpenDynaset)
strTest0 = [Forms]![Shelflife]![Lb_Armazem].Column(0)
strTest1 = [Forms]![Shelflife]![Lb_Armazem].Column(1)
strTest2 = [Forms]![Shelflife]![Lb_Armazem].Column(2)
strTest3 = [Forms]![Shelflife]![Lb_UtiOfi].Column(4)
str = "SELECT SLProd_In.PN, SLProd_In.TR, SLProd_In.Dt_L, SLProd_Out.TAP_Num" _
& " FROM SLProd_Out,SLProd_In WHERE (((SLProd_In.PN)='" & strTest0 & "') AND ((SLProd_In.TR)=" & strTest1 & ") AND ((SLProd_In.Dt_L)=" & strTest2 & ") AND ((SLProd_Out.TAP_Num)=" & strTest3 & "))"
Set rcd2 = Base.OpenRecordset(str)

when we click Debug it goes directly to the line "Set rcd2 = Base.OpenRecordset(str)"

hope u guys can help me
Thanks in advance

str is the name of a VBA funtion, try changing this to something else.

 

[gemma-the-husky]

and note that constructs like this

Dim str, strTest0 As String
Dim strTest1, strTest2, strTest3 As Double

are not correct

in line 2 the variables

strtest1, and strtest2 will be variants, not doubles

every variable has to be individually typed in vba

 

[CliffPartridge]

Having spent a few hours on this same problem, I thought it would be good to post the fix for it.

The error MAY be nothing to do with the SQL but with the call to "openRecordSet".

"openRecordSet" exists in the DAO and the ADO libraries and each requires different parameters. Code that works great in one Access database can suddenly develop this error when copied to a different Access database.

To correct the runtime error, make explicit reference to the library you are using. For example

Dim db As Dao.Database
Dim rs As Dao.Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT * FROM aTABLE", dbOpenDynaset)