Access SQL query in ASP page

[Terry]

I am using an access backend and need to query by current date. For example
- late items are items where the due date is >= today (current date). My
sql statement looks like this

SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"

I know that Access has funny little caveats like adding # to the start and
end of dates but I still cannot seem to get this to work. Can anyone give
me a hint?

Thanks

I was also thinking that my problem may have something to do with the way
I am connecting and the type of query I am using - so here's my code if anyone
is interested

<%
Dim Conn
Dim rs

Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

strDatabasePath = Server.MapPath("tracking.mdb")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath
& ";"

SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"

rs.Open SQL, Conn

rs.MoveFirst
howManyFields = rs.Fields.Count
Response.Write sql
%>

[garetjax]

Well, I too once went through this and had the same issue...I figured out
that what your posting is considered a text element whereas the Field value
in the database is a Date/Time Element.

Requiring: #DATE#
The Comparison that your doing must also, contain this same format if I'm
not mistaken. But, in my dev I also found that the field I was using contained
Date/Time Stamp rather than a simple date. So, I had to part out what I
wanted to match... ie

SELECT * FROM tblMain WHERE Month(zDATE)='" & Month(reqDate) & "' AND WHERE
YEAR(zDate)='" & Year(reqDate) & "'"

Something like that anyways.


"Terry" <snakbrat@yahoo.com> wrote:
>
>I am using an access backend and need to query by current date. For example
>- late items are items where the due date is >= today (current date). My
>sql statement looks like this
>
>SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
>
>I know that Access has funny little caveats like adding # to the start and
>end of dates but I still cannot seem to get this to work. Can anyone give
>me a hint?
>
>Thanks
>
>I was also thinking that my problem may have something to do with the way
>I am connecting and the type of query I am using - so here's my code if
anyone
>is interested
>
><%
>Dim Conn
>Dim rs
>
>Set Conn = Server.CreateObject("ADODB.Connection")
>Set rs = Server.CreateObject("ADODB.Recordset")
>
>strDatabasePath = Server.MapPath("tracking.mdb")
>Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath
>& ";"
>
>SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
>
>rs.Open SQL, Conn
>
>rs.MoveFirst
>howManyFields = rs.Fields.Count
>Response.Write sql
>%>
>
>

[Q*bert]

Just curious... Why do you have to pass in the date if SQL provides a date
function?
SQL = "SELECT * FROM database where duedue >= Date() " --> MSAccess
SQL = "SELECT * FROM database where duedue >= GetDate() " -->SQL SEVER

"garetjax" <fusion_box@hotmail.com> wrote:
>
>Well, I too once went through this and had the same issue...I figured out
>that what your posting is considered a text element whereas the Field value
> in the database is a Date/Time Element.
>
>Requiring: #DATE#
>The Comparison that your doing must also, contain this same format if I'm
>not mistaken. But, in my dev I also found that the field I was using contained
>Date/Time Stamp rather than a simple date. So, I had to part out what I
>wanted to match... ie
>
>SELECT * FROM tblMain WHERE Month(zDATE)='" & Month(reqDate) & "' AND WHERE
>YEAR(zDate)='" & Year(reqDate) & "'"
>
>Something like that anyways.
>
>
>"Terry" <snakbrat@yahoo.com> wrote:
>>
>>I am using an access backend and need to query by current date. For example
>>- late items are items where the due date is >= today (current date).
My
>>sql statement looks like this
>>
>>SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
>>
>>I know that Access has funny little caveats like adding # to the start
and
>>end of dates but I still cannot seem to get this to work. Can anyone give
>>me a hint?
>>
>>Thanks
>>
>>I was also thinking that my problem may have something to do with the way
>>I am connecting and the type of query I am using - so here's my code if
>anyone
>>is interested
>>
>><%
>>Dim Conn
>>Dim rs
>>
>>Set Conn = Server.CreateObject("ADODB.Connection")
>>Set rs = Server.CreateObject("ADODB.Recordset")
>>
>>strDatabasePath = Server.MapPath("tracking.mdb")
>>Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath
>>& ";"
>>
>>SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
>>
>>rs.Open SQL, Conn
>>
>>rs.MoveFirst
>>howManyFields = rs.Fields.Count
>>Response.Write sql
>>%>
>>
>>
>