How to select records created between or on certain dates in Access?

[WuCOng]

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&quot
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.

 

[shaddow]

The code seems ok to me but, i still dont know in witch table is "date_sub" maybe u need to put his table alias in place...
One more thing, if u are using ACCESS (as i see u are) u need to put " instead of ' to compare with an string value
the line ... s.service_type = '"&servicetype&"' GROUP BY
should be ... s.service_type = """&servicetype&""" GROUP BY
U need to replace every " in an string with "" to include one in...

Hope this helps and if u need more help please ask... ________

George, M

 

[Nickman]

Hi

this should work:

WHERE date_sub=cDate(#" & varNow & "#)

 

[WuCOng]

shaddow - the code works fine without the date bit in it even with the ' in place. The dat_sub is from the compliants table but even with the c.date_sub alias the result is the same.

I'll try your method Nickman, thanks, but why does this work and not my method?

 

[shaddow]

U need to put " not ' ________
George, M
email : shaddow11_ro@yahoo.com

 

[WuCOng]

shaddow - but even with ' all my other recordesets are generated fine. The only problem is when using dates, and then you have to use # anyway.

Neither of these solutions worked. The date clause is just ignored and a recorset of all data, regardless of date_sub, is generated. Nickman - the cDate() produces no error but it gives the same result as if I just used #&varNow&#.

Baffled.