Date range query for ASP page

[icy111]

Dear all,

I am generating a report in ASP Platform using Microsoft Access 2000 as the Backend database.

Here is my query in the ASP page:
The dteFromDate : 10/10/2003 'MM/DD/YYYY
The dteToDate : 10/14/2003 'MM/DD/YYYY

Dim strSQL
Dim dteFromDate
Dim dteToDate

strSQL = ""
strSQL = " SELECT * "
strSQL = strSQL & " FROM Table1 "
strSQL = strSQL & " WHERE Table1.CreatedDate = #" & dteFromDate & "#"
strSQL = strSQL & &quot; AND Table1.CreatedDate <= #&quot;
strSQL = strSQL & dteTodate & &quot;#&quot;
strSQL = strSQL & &quot; ORDER BY ID &quot;

The CreatedDate data type is Date/Time.
The format is General Date.

I always get the error as Type Mismatched.

Is there solution for this..??

Thanks a lot

Regards
icy111

 

[Golom]

I don't see the code where &quot;10/10/2003&quot; (for example) is being assigned to &quot;dteFromDate&quot; but one possible problem is that

   Dim dteFromDate

creates a variant and a statement like

   dteFromDate = "10/10/2003"

will create a String variant subtype ... not a date/time. That flows into the SQL where it is attempting to compare a Date/Time field to a String (i.e. type mismatch). If you explicitly declare the &quot;dteFromDate&quot; and dteToDate&quot; variables As Date then you're sure that apples are being compared to apples.

BTW: Your clause reads

   " WHERE Table1.CreatedDate = #" & dteFromDate  & "#"

Do you mean &quot;>=&quot;?

 

[cmmrfrds]

You don't show how you are executing the sql statement. I assume it is through an ADO object. You may need to surround the date with single or double quotes. I believe either will work. chr(34) = &quot;&quot; chr(39) = '

strSQL = strSQL & &quot; WHERE Table1.CreatedDate = '&quot; & dteFromDate & &quot;'&quot;
or
strSQL = strSQL & &quot; WHERE Table1.CreatedDate = &quot; & chr(34) dteFromDate & chr(34)
or
strSQL = strSQL & &quot; WHERE Table1.CreatedDate = &quot; & chr(39) dteFromDate & chr(39)

 

[icy111]

Thanks for the response.
But i thought in VBSCript, we don't have to declare the Data Type.

I have used the single quote ' ' in my query but it still cannot work. That's y i hhave to use the # in my query


Actually, i have few scenarios to generate a report based on :

Scenario 1. On this partuclar Date and that particular time slots.
eg. 10/14/2003 9:10:01AM
eg. 10/14/2003 9:10:15AM
eg. 10/14/2003 9:10:25AM
From these 3 DateTime, i need to generate a report.

Scenario 2. a Date Range eg Between Date1 and Date2
eg. From 06/11/2003 To 10/14/2003
From these Date Range, i need to generate a report.

Sorry if i didn't make myself clear.