*RESOLVED* SQL statement "Type Mismatch" error

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

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?

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:confused:

JO

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

VB Code:

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. :)

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

JO

Quote:

---

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

JO

---

Code:

---

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

---

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

JO

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