*RESOLVED* SQL statement "Type Mismatch" error

[joltremari]

I'm building sql on the fly based on user input. Everything works except this one that selects between two dates. I get the error: "Data type mismatch in criteria expression" at the line: rs.Open sql

This is the sql statement:

SELECT * FROM WOINFO WHERE (ConstDate BETWEEN "#4/1/2002#" AND "#4/30/2002#")

I tried doing this with a variable because the field ConstDate is a Date field in the DB:

SELECT * FROM WOINFO WHERE (ConstDate BETWEEN "#CDate(strCONDATE)#" AND "#CDate(strCONDATE2)#")

But I still get type mismatch.

If anyone spots something wrong with this any help is appreciated.

JO

[BestCoder]

I'm not sure I understand why you are enclosing your dates (whether in a variable or not) in double quotes " "? Also are the variables VB variables?

[joltremari]

They are vb variables

Are you not supposed to put dates in double quotes, I thought the syntax was "#DateHere#"

If it is not then that must be my trouble

JO

[BestCoder]

That could be... however if you want to use VB variables then, your code should look like this:

strSQL = "SELECT * FROM WOINFO WHERE (ConstDate BETWEEN #" & strCONDATE "# AND #" & strCONDATE2 & "#)"

[added] Dates are not supposed to be inside double-quotes... just the pound signs.

[added 2] that is for SQL. In VB, they go in quotes.

[joltremari]

How would it be written if I do not use a variable, if I "hardcode" certain dates in?

JO

[andreys]

Originally posted by joltremari
How would it be written if I do not use a variable, if I "hardcode" certain dates in?

JO

strSQL = "SELECT * FROM WOINFO WHERE (ConstDate BETWEEN #12/2/02# AND #12/31/02#)"

[joltremari]

Sorry, I never said "thank you" that got me working. The syntax in sql statements always gets me

JO

[BestCoder]

No Problem! Glad we could help. I am sure that andreys would say the same.