Solved

sql query syntax

Posted on 2006-11-15
4
267 Views
Last Modified: 2012-08-14
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
Comment
Question by:totallymarc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 125 total points
ID: 17948905
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
 
LVL 4

Expert Comment

by:carlmahon
ID: 17948933
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
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17948961
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
 

Author Comment

by:totallymarc
ID: 17948969
Thank you kindly, works fantastic now.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question