I'm using this code:
dim SQLstr, oRS, monthchecker, varNow
varNow = Date()
'CALCULATE A DATE THAT IS 6 MONTHS AGO
monthchecker = varNow - 183
set oRS = server.createObject("ADODB.Recordset"
SQLstr="SELECT s.service_name, COUNT(c.complaint_no) AS num_comps FROM complaints c, services s WHERE date_sub = #"&28/8/01&"# AND c.service_name = s.service_name AND s.service_type = '"&servicetype&"' GROUP BY s.service_name"
to try to select only records created (submitted) on the 28/8/01.
I want to extend the code to include:
WHERE date_sub BETWEEN #"&monthchecker&"# AND #"&varNow&"#
so that only records created (submitted) in the last 6 months are included in the recordset.
My table has records that fit this criteria (both created in the last 6 months and created on 28/8/01) but the recordset is empty.
Why? The code seems fine to me, I can't see the problem. Anyone able to help? Much appreciated.
dim SQLstr, oRS, monthchecker, varNow
varNow = Date()
'CALCULATE A DATE THAT IS 6 MONTHS AGO
monthchecker = varNow - 183
set oRS = server.createObject("ADODB.Recordset"
SQLstr="SELECT s.service_name, COUNT(c.complaint_no) AS num_comps FROM complaints c, services s WHERE date_sub = #"&28/8/01&"# AND c.service_name = s.service_name AND s.service_type = '"&servicetype&"' GROUP BY s.service_name"
to try to select only records created (submitted) on the 28/8/01.
I want to extend the code to include:
WHERE date_sub BETWEEN #"&monthchecker&"# AND #"&varNow&"#
so that only records created (submitted) in the last 6 months are included in the recordset.
My table has records that fit this criteria (both created in the last 6 months and created on 28/8/01) but the recordset is empty.
Why? The code seems fine to me, I can't see the problem. Anyone able to help? Much appreciated.