Data Type Mismatch on Dates Search 1

[thunderain]

Im my program I am doing several searches on the database. I have a
problem with dates. My first search to equal a date gives no error. Works
fine. A second search to find a date inside a range gives a data type
mismatch error. I don't know why I would get this here when the equals
seach does not give this error. It seems that using anything other then "Like" gives data mismatch when dealing with dates. My inside range and all other searchs work with anything other then dates.

ERROR MESSAGE:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
expression.


EQUALS SEARCH CODE (this works fine):


If Request.Form(&quot;OccEntryDate&quot <> &quot;&quot; AND Request.Form(&quot;ChoiceOccEntryDate&quot = &quot;equals&quot; Then

occValue = Request.Form (&quot;OccEntryDate&quot

Set objRS3 = objCon.Execute(&quot;SELECT * FROM tblView WHERE SubcategoryID=1 AND FieldType='date'&quot
fieldname = objRS3(&quot;FieldName&quot

Set objRS1 = objCon.Execute(&quot;SELECT SelectClause, viewID FROM tblSubCategory WHERE SubCategoryID=1&quot strSQL = objRS1(&quot;SelectClause&quot & &quot; AND (&quot; & fieldname & &quot; Like'&quot; & occValue & &quot;') ORDER BY &quot; & fieldname
objRS1.Close

End If


INSIDE RANGE CODE (gives error above):

If Request.Form(&quot;OccEntryDate&quot <> &quot;&quot; AND Request.Form(&quot;OccEntryDate2&quot <> &quot;&quot; AND Request.Form(&quot;ChoiceOccEntryDate&quot = &quot;insiderange&quot; Then

occValue = Request.Form (&quot;OccEntryDate&quot
occValue2 = Request.Form (&quot;OccEntryDate2&quot

Set objRS3 = objCon.Execute(&quot;SELECT * FROM tblView WHERE SubcategoryID=1 AND FieldType='date'&quot
fieldname = objRS3(&quot;FieldName&quot

Set objRS1 = objCon.Execute(&quot;SELECT SelectClause, viewID FROM tblSubCategory WHERE

SubCategoryID=1&quot
strSQL = objRS1(&quot;SelectClause&quot & &quot; AND (&quot; & fieldname & &quot; Between '&quot; & occValue & &quot;' AND '&quot; & occValue2 & &quot;') ORDER BY &quot; & fieldname

objRS1.Close

End If


I also tried this line (which didn't work):

strSQL = objRS1(&quot;SelectClause&quot & &quot; AND (&quot; & fieldname & &quot; > '&quot; & occValue & &quot;' AND < '&quot; & occValue2 & &quot;') ORDER BY &quot; & fieldname


Here are select statments printed out:

This one works, equal search

SELECT * FROM tblTransaction WHERE dateExit IS NULL AND (dateEntry Like'11/19/2001 12:04:06 PM')

ORDER BY dateEntry

This one give error above, inside range search

SELECT * FROM tblTransaction WHERE dateExit IS NULL AND (dateEntry Between'11/17/2001 12:04:06 PM' AND '11/19/2001 12:04:06')) ORDER BY dateEntry


Any ideas would be appreciated.
Thank you
Thunderain

 

[Jorgandr]

let me just point out when you are comparing values in your SQL statement here are the options you can have

'&quot; & Fieldhere & &quot;' indicates this is a string
&quot; & Fieldhere & &quot; indicates this is a number
#&quot; & Fieldhere & &quot;# indicates this is a date field
so it might be that simple

instead of '&quot; & occValue & &quot;' try #&quot; & occValue & &quot;#