sql query syntax

0
August 17, 2011

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.

Comments See all(0)

Add comment
0
August 17, 2011

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
August 17, 2011

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
August 17, 2011

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
August 17, 2011

Thank you kindly, works fantastic now.

Related Questions

Search Form & SQL Query Syntax In ASP

I have a form with muliple fields on it which is used as a search form, the user can enter any number of fields and search on the database. The sql query i wrote looks like this: SQL = "SELECT * Read More

Views

33

Votes

0

Answers

4

August 17, 2011

Complex SQL Query Syntax Problem

I have a patient scheduling system and I am creating a report to show physicians who have referred patients and those who have not during a given time period, as well as the number of Referrals from e Read More

Views

53

Votes

0

Answers

20

August 15, 2011

SQL Query Syntax when using multiple searches

Hi Experts! I have a form that prints out crystal report where there are 8-10 search fields such as DOB, Zip etc. I have written an SQL Query that fetches data correctly, but i am not sure how to writ Read More

Views

37

Votes

0

Answers

9

August 16, 2011

SQL Query embedded in xml page in asp.net website, need help with syntax please

Hi, I need to convert this query that populates a dropdown list in a asp.net website into plain sql so I can run it in the sql server management studio and view the results. Then I probably need to ma Read More

Views

38

Votes

0

Answers

4

August 17, 2011

SELECT SELECT SQL Query Result

I have a SQL query syntax and when i run it, it will return result table in SQL SERVER. What I want is to query using the result. Is that any solution? Eg. The result returned is a table, and i wish t Read More

Views

14

Votes

0

Answers

6

August 17, 2011

Dynamic ASP.net SQL Query

I am trying to produce a dynamic asp.net sql query that returns data based on the data inputted into 5 text boxes. I only want the fields where the user has entered a value to be queried. i.e. if i ha Read More

Views

24

Votes

0

Answers

16

August 17, 2011

SQL Query syntax : IF NOT EXISTS?

First I creat a Login to SQL Server using CREATE LOGIN NT AUTHORITY\NETWORK SERVICE FROM WINDOWS WITH DEFAULT_DATABASE="MyDB"; Here, NT AUTHORITY\NETWORK SERVICE is the name of the Login I c Read More

Views

48

Votes

0

Answers

6

August 16, 2011

update SQL Query in VBA Using Variables

I am trying to use an update query but cannot get the syntax correct.  Please advise.  This is obviously my hardest thing to grasp (SQL Query syntax with variables). For Each varItm In Me.ls Read More

Views

9

Votes

0

Answers

3

August 16, 2011

RemoteApp + MSI + GPO  The following error was encountered: SQL query syntax invalid or unsupported.

Hello, I have a win2k3 AD with a win2k8 TS. Now i have made some msi files for remoteapp (done that through the wizard within Terminal Server). I`m trying to deploy them through a GPO. When i do that Read More

Views

7

Votes

0

Answers

14

August 17, 2011

Difficult sql query  how can select all account ( child account for   parent root and father)    with credit   and debit  from another table

i have table named is tbl_account  with this field: Fld_ACCOUNT_ID Fld_PARENT_ID Fld_ACCOUNT_NAME Fld_AMOUNT this table for  account tree when  i want a query which displays the amount Read More

Views

42

Votes

0

Answers

5

August 16, 2011

SQL / Microsoft Query - two sets of conditions in two tables

I'm querying a complex database and can get certain portions of the query to work. I'm having trouble, however, with one area. The relevant portion of the SQL query (which works) looks like th Read More

Views

57

Votes

0

Answers

4

August 17, 2011

XML representation of SQL query

Hello, I'm trying to construct a DTD for representing any SQL query in XML. Before doing it myself I was wondering if any standard or proposition exists in the XML world for doing such a thing. Ba Read More

Views

28

Votes

0

Answers

6

August 17, 2011

help with modifying a sql query probably using subselect

I have the following SQL query which is working fine however I want to make a change to it. Rather than summing up the 'ishighspam' column I would like to determine the number of occurances wh Read More

Views

25

Votes

0

Answers

8

August 17, 2011

Dynamic SQL Query Problems with Quotes

Thought this would be easier... Trying to pass a dynamic where clause to a SQL query in Coldfusion (using SQL Server 7)  The where clause is passed in from a form field.   A simplified versi Read More

Views

39

Votes

0

Answers

6

August 16, 2011

Getting syntax error when running SQL query with PHP on Drupal site.

First, let me explain that I have not "tinkered" with any web development in quite some time.  So, I am an antiquated newb and apologize for any ignorance that persues... I have Drupal Read More

Views

39

Votes

0

Answers

2

August 16, 2011

SQL Query Syntax question

Hello, I have a bunch of records that relate to locations.  For each location there will be records for the inventory there, daily.  When that location is emptied there is no record change, Read More

Views

10

Votes

0

Answers

5

August 17, 2011

Query Syntax to prompt user for a value

Hello All thanks in advance for your help. I would like create a query that prompts the user for input as follows: In a table of people I would like to query by name. If I put [Please enter a name] in Read More

Views

7

Votes

0

Answers

7

August 15, 2011

Create Date Variable in Query String using 2 Fields and hard coded 2011 year and then test that date variable to see if we are 14 days later than that date

Dim myDateTime as Date for example  tblTesterVariables.contact month1 = 5 for example tblTesterVariables.contact date1 = 6 year = 2011 so myDateTime would = 5/6/2011 Want to create a (month, dayd Read More

Views

5

Votes

0

Answers

4

August 16, 2011

How can I retrieve a specific number of records in an Access database using SQL?

I want to query an Access database to return a specific number of records. For example, the first 500 rows, or rows 501 to 600. Does anyone know the SQL syntax for this? Read More

Views

12

Votes

0

Answers

7

August 16, 2011

Cannot deploy Service pack 4 on  2003 server GPO keep getting sql error

Im trying to deploy sp4 on our domain but keep getting an error on the DC application event viewer. "Software Installation was unable to read the MSI file \rd-dns\sw\i386\update\up date.msi. &#16 Read More

Views

22

Votes

0

Answers

9

August 16, 2011