• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

sql query syntax

Hi,

We have an application that makes a selection from a Access database based on on date that is passed in. For example if we pass in 10/26/2006, it would select all items that match that month in a date field. This has been working great, but for some reason when it comes to December/January, those 2 months pull up the same data.

Here is the statement that has been used to date:

SQL = "SELECT * FROM tbl_cm_schedule WHERE cid=" & Session.Contents("UserID") & " AND (DATEDIFF('m',#" & dDate & "# , cdate) = DATEDIFF('yyyy', #" & dDate & "#, cdate)) ORDER BY cdate"

So if you're viewing data in December, you see both December and January data, and in January you see both Jan. and Dec. data.

Any help is appreciated.
Thanks.
0
totallymarc
Asked:
totallymarc
  • 2
1 Solution
 
VoteyDiscipleCommented:
I'm not sure, to be honest, why that code works for other months.  It's looking for dates where the difference in years is equal to the difference in months?  

In any case, the following should be better..

SQL = "SELECT * FROM tbl_cm_schedule WHERE cid=" & Session.Contents("UserID") & " AND (MONTH(#" & ddate & "#) = MONTH(cdate) AND YEAR(#" & ddate & "#) = YEAR(cdate)) ORDER BY cdate"

0
 
carlmahonCommented:
This is because for October
DATEDIFF('m',#" & dDate & "# , cdate) = 0
DATEDIFF('yyyy', #" & dDate & "#, cdate)) = 0

As you get to end of year then you have issues. Because a JAN/DEC Dadtediff for the month = 1 and the years also = 1.


VoteyDiciple's SQL will work
0
 
VoteyDiscipleCommented:
I guess what confused me is there ought to be data for, say, February 2004 showing up in the April 2006 results as well.  Unless the data just don't go back that far, in which case this problem would have become more extensive as more years passed.  (-:
0
 
totallymarcAuthor Commented:
Thank you kindly, works fantastic now.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now