Date query problems

[dawilis]

Im need to extract all records in a certain month but I keep getting funny outputs if someone could shed some light here it would be aprreciated

THis SQL produces the line below this
SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= "&viewdate1&" AND contactDate <="&viewdate2

SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= #1/8/2003# AND contactDate <= #31/8/2003#

this output works for august ok displaying the records in that month, however if I select november it displays all records
if i select before this month it shows no records, why is it displaying all records when I select months after this month??
I have the same results when i use the 'Between' tag

[dawilis]

Originally posted by dawilis
Im need to extract all records in a certain month but I keep getting funny outputs if someone could shed some light here it would be aprreciated

THis SQL produces the line below this
SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= "&viewdate1&" AND contactDate <="&viewdate2

SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= #1/8/2003# AND contactDate <= #31/8/2003#

this output works for august ok displaying the records in that month, however if I select november it displays all records
if i select before this month it shows no records, why is it displaying all records when I select months after this month??
I have the same results when i use the 'Between' tag

[raf]

You should use the like-operator and then check on the month. Like this

wantedmonth='08' 'this value can come from a dropdown or if you need the current month, use month(Now)
SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate LIKE '%/" & wantedmonth & "/%'"

[A1ien51]

Is the date being stored in the database in that format?

That could be the problem.

[dawilis]

the date in the d/b is dd/mm/yy
as in the sql querystring, it will work ok however if i change the format to mm/dd/yy, but still with the problems mentioned above

[dawilis]

thanks A1ien51 the like worked a treat

[raf]

Ahum, ahum ...

Anyway, i spotted a possible problem --> the year probably shoulnt be a wildcard.
so your code would then be

wantedmonth='08' 'this value can come from a dropdown or if you need the current month, use month(Now)
wantedyear='03' 'idem but right(year(Now),2)
SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate LIKE '%/" & wantedmonth & "/" & wantedyear & "'"