[RESOLVED] Type mismatch on date in Where statement

Have a problem with error Data type Mismatch in criteria expression

Code:

---

" WHERE Data.[date tested] = '2/16/2007/';"

---

checked table and field is data type Date/Time Tried all different formats
12/12/2001 #12/12/2001# *12/12/2001* not sure if there is not a bug .
using access 2000 with ADO

Code:

---

" WHERE Data.[date tested] = #2/16/2007# ;"

---

this should be correct i believe

Quote:

---

" WHERE Data.[date tested] = '2/16/2007/';"

---

You have an extra slash at the end of the date

try this

" WHERE Data.[date tested] = '02/16/2007';"

Hope this helps...

Yes you are right

Code:

---

" WHERE Data.[date tested] = #2/16/2007# ;"

---

Now Im trying to get the date from the form Text Box
Keep geting error " Data type mismatch in criteria expression"
BeginDate and EndDate text box's I have tryed add# on end with
Dim bdate as string
set bdate = "#" BeginDate "#"
Then put var in where sta. '" & bdate & "'

still no luck!

even dim as other type long,inger,varent don't know.

Code:

---

Data.[date tested] > '" & BeginDate & "' And Data.[date tested] < '" & EndDate & "'

---

Quote:

---

Originally Posted by Scaleman

Yes you are right

Code:

---

" WHERE Data.[date tested] = #2/16/2007# ;"

---

Now Im trying to get the date from the form Text Box
Keep geting error " Data type mismatch in criteria expression"
BeginDate and EndDate text box's I have tryed add# on end with
Dim bdate as string
set bdate = "#" BeginDate "#"
Then put var in where sta. '" & bdate & "'

still no luck!

even dim as other type long,inger,varent don't know.

Code:

---

Data.[date tested] > '" & BeginDate & "' And Data.[date tested] < '" & EndDate & "'

---

---

Hey there...
I think the main problem is you are using strings in the criteria ...eg. you have used ' before closing the qry string and then ' again before starting the query string...

...d] > '" & BeginDate & "' And...

the SQL whill interpret the dates as strings and not dates... just put the # in place of it eg...

...d] > #" & BeginDate & "# And...

and don't use them in your begindate and enddate variables. You want the SQl to look like this when it is submitted by your program...

Data.[date tested] > #mm/dd/yyyy# And Data.[date tested] < #mm/dd/yyyy#

and not this

Data.[date tested] > '#mm/dd/yyyy#' And Data.[date tested] < '#mm/dd/yyyy#'


incidentally you could rewrite the sql as

data.[date tested] between #mm/dd/yyyy# and #mm/dd/yyyy#

cheers

try to print out your query to the immediate window so you can see it looks like it is correct

Hey Guy's
That did the trick!
Thanks alot for the help
hope i can be of some help someday
Scamenan