Passing a date parameter to an Access query in ASP

[scf1984]

I have the followig code:

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider=provider
conn.Open dbpath
set rs = Server.CreateObject("ADODB.recordset")
 
...
 
 
sql="SELECT department, job, start, [end] FROM Qry_AssignedWeek WHERE employee='" & employee & "' ORDER BY start"
rs.open sql, conn, sunday

Where the Qry_AssignedWeek is the following query in Access2003:

PARAMETERS sunday DateTime;
SELECT *
FROM Qry_AssignedNotCancelled
WHERE (((Qry_AssignedNotCancelled.start) Between [sunday] And [sunday]+7)) OR (((Qry_AssignedNotCancelled.end) Between [sunday] And [sunday]+7));

this code generates the ASP error:

Provider (0x80020005)
Type mismatch.
/schedule/ViewMyShifts.asp, line 70


The "sunday" variable is a string representing a date in format "dd/mm/yyyy"

I have tried to change the "sunday" in "rs.open sql, conn, sunday" with many different options:
int(sunday)
CDate(sunday)
#23/09/2007#

etc.

All returning various errors.

Please, can anyone guide me around this problem?

[markrawlingson]

You could try something like this... and scrap the msAccess query all together.

This would calculate when sunday is, and return all records which fall within the last sunday to the next sunday. In the case of this week, as an example, it would return all records from Sunday the 15th of september to Sunday the 22nd of september.

If WeekDay(Date()) = 1 Then 'it's sunday
   dDateStart = Date() ' it's sunday today so we want to start from today's date
Else
   'Today is not sunday, so we need to find when sunday is. To do this, we'll find the integer value of the weekday, and count backwards to sunday.
   'If today is friday weekDay will return 6 - sunday would be 5 days ago. So weekday*-1 = -6 + 1 = -5
   iDateChange = (WeekDay(Date())*-1)+1
   dDateStart = DateAdd(d,iDateChange,Date())
End If
'Now we have our start date, we just need our end date, which is easy
 
dDateEnd = DateAdd(d,7,dDateStart)
 
sSQL="SELECT department, job, start, datefield FROM yourtable WHERE employee='" & employee & "' AND datefield BETWEEN " & dDateStart& " AND " & dDateEnd & " ORDER BY datefield"
rs.open sql, conn, 3, 3
 
 

I also don't know why you have rs.open sql, conn, sunday

You can't pass a variable through your recordset opening statement. This statement accepts: your sql query, the connection string to your db, and your vb constants. (adReadOnly, adLockOptomistic, etc)

Hope this helps,

Sincerely
Mark

I have the followig code:

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider=provider
conn.Open dbpath
set rs = Server.CreateObject("ADODB.recordset")
 
...
 
 
sql="SELECT department, job, start, [end] FROM Qry_AssignedWeek WHERE employee='" & employee & "' ORDER BY start"
rs.open sql, conn, sunday

Where the Qry_AssignedWeek is the following query in Access2003:

PARAMETERS sunday DateTime;
SELECT *
FROM Qry_AssignedNotCancelled
WHERE (((Qry_AssignedNotCancelled.start) Between [sunday] And [sunday]+7)) OR (((Qry_AssignedNotCancelled.end) Between [sunday] And [sunday]+7));

this code generates the ASP error:

Provider (0x80020005)
Type mismatch.
/schedule/ViewMyShifts.asp, line 70


The "sunday" variable is a string representing a date in format "dd/mm/yyyy"

I have tried to change the "sunday" in "rs.open sql, conn, sunday" with many different options:
int(sunday)
CDate(sunday)
#23/09/2007#

etc.

All returning various errors.

Please, can anyone guide me around this problem?

[scf1984]

Thanks! it's working:

sunday = CDate(sunday)
sql="SELECT department, job, start, [end] FROM Qry_AssignedNotCancelled WHERE employee='"
sql=sql & employee & "' " & "AND start BETWEEN #" & sunday & "# AND #" & sunday + 7 & "#"
 
rs.open sql, conn

(the varaiable "sunday" is given as a string "dd/mm/yyyy")

But I am still quite puzzled...
Is there a way to run an Access parameterized query from ASP?

You could try something like this... and scrap the msAccess query all together.

This would calculate when sunday is, and return all records which fall within the last sunday to the next sunday. In the case of this week, as an example, it would return all records from Sunday the 15th of september to Sunday the 22nd of september.

If WeekDay(Date()) = 1 Then 'it's sunday
   dDateStart = Date() ' it's sunday today so we want to start from today's date
Else
   'Today is not sunday, so we need to find when sunday is. To do this, we'll find the integer value of the weekday, and count backwards to sunday.
   'If today is friday weekDay will return 6 - sunday would be 5 days ago. So weekday*-1 = -6 + 1 = -5
   iDateChange = (WeekDay(Date())*-1)+1
   dDateStart = DateAdd(d,iDateChange,Date())
End If
'Now we have our start date, we just need our end date, which is easy
 
dDateEnd = DateAdd(d,7,dDateStart)
 
sSQL="SELECT department, job, start, datefield FROM yourtable WHERE employee='" & employee & "' AND datefield BETWEEN " & dDateStart& " AND " & dDateEnd & " ORDER BY datefield"
rs.open sql, conn, 3, 3
 
 

I also don't know why you have rs.open sql, conn, sunday

You can't pass a variable through your recordset opening statement. This statement accepts: your sql query, the connection string to your db, and your vb constants. (adReadOnly, adLockOptomistic, etc)

Hope this helps,

Sincerely
Mark