...

View Full Version : Date query problems



dawilis
11-16-2003, 01:55 PM
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
11-16-2003, 01:58 PM
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
11-16-2003, 02:22 PM
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
11-16-2003, 03:36 PM
Is the date being stored in the database in that format?

That could be the problem.

dawilis
11-16-2003, 11:01 PM
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
11-16-2003, 11:17 PM
thanks A1ien51 the like worked a treat

raf
11-16-2003, 11:48 PM
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 & "'"



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum