QUERY CALCULATION BASED ON DATE RANGE?

L

Liam.M

Hey guys,

If someone could spare the time in helping me with this problem it
would be very much appreciated...and apologies if it seems like a very
basic questions, however, I do not possess the experience and knowledge
like many of the people on here, and very much appreciate your
assistance.

I need to modify my SQL Statement to filter out all the records that I
am not interest in, bascially I need this query only showing records
that fall within a certain Date range...based on my "DueDate"
field......

The SQL Statement is as follows:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],
ShipsInformation.EmailAddress
FROM ShipsInformation;

Now I understand that I need to add a "WHERE" criteria for the date
section....I want this query to only show records that fall within the
next two months of the "Due Date"....so something along the lines of :
WHERE DueDate <=DateAdd(Date,"m,",-2) ?


If someone could please explain how I could implement such a Query it
would be much appreciated...

Kind Regards


Liam.
 
P

Phil

WHERE DueDate <=DateAdd(Date,"m,",-2) ? should read something like

WHERE ShipsInformation.[Date of Issue] <=DateAdd("m",-2,Date()),

and should be placed at the end of your SQL after the FROM line.

Two mistakes/potential problems. You got the syntax for DateAdd
backwards, and you referenced an alias. Access does not always like it
when you reference alias's, for purposes of criteria, sorting, etc. it
is safest to reference the original field, not the alias.
 
L

Liam.M

Hey Phil, thankyou so much for your prompt reply! I have tried to
implement this, however I am getting a few different error messages
My SQL Statement looks like this:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],FROM
ShipsInformation; WHERE ShipsInformation.[Date of
Issue]<=DateAdd("m",-2,Date());

I once of two errors appearing...one: The SELECT statment includes a
reserved word or an argument name that is mispelled or missing, or the
punctuation is incorrect...or two: Characters found after SQL
Statment....any suggestions?

Regards

Liam
WHERE DueDate <=DateAdd(Date,"m,",-2) ? should read something like
Click to expand...

WHERE ShipsInformation.[Date of Issue] <=DateAdd("m",-2,Date()),

and should be placed at the end of your SQL after the FROM line.

Two mistakes/potential problems. You got the syntax for DateAdd
backwards, and you referenced an alias. Access does not always like it
when you reference alias's, for purposes of criteria, sorting, etc. it
is safest to reference the original field, not the alias.






Hey guys,

If someone could spare the time in helping me with this problem it
would be very much appreciated...and apologies if it seems like a very
basic questions, however, I do not possess the experience and knowledge
like many of the people on here, and very much appreciate your
assistance.

I need to modify my SQL Statement to filter out all the records that I
am not interest in, bascially I need this query only showing records
that fall within a certain Date range...based on my "DueDate"
field......

The SQL Statement is as follows:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],
ShipsInformation.EmailAddress
FROM ShipsInformation;

Now I understand that I need to add a "WHERE" criteria for the date
section....I want this query to only show records that fall within the
next two months of the "Due Date"....so something along the lines of :
WHERE DueDate <=DateAdd(Date,"m,",-2) ?


If someone could please explain how I could implement such a Query it
would be much appreciated...

Kind Regards


Liam.
Click to expand...
Click to expand...
 
L

Liam.M

Apologies Phil....I have now fixed the problem....Kind Regards

Liam

p.s: thankyou so much for your assistance it is very much appreciated!

[email protected] said:
Hey Phil, thankyou so much for your prompt reply! I have tried to
implement this, however I am getting a few different error messages
My SQL Statement looks like this:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],FROM
ShipsInformation; WHERE ShipsInformation.[Date of
Issue]<=DateAdd("m",-2,Date());

I once of two errors appearing...one: The SELECT statment includes a
reserved word or an argument name that is mispelled or missing, or the
punctuation is incorrect...or two: Characters found after SQL
Statment....any suggestions?

Regards

Liam
WHERE DueDate <=DateAdd(Date,"m,",-2) ? should read something like
Click to expand...

WHERE ShipsInformation.[Date of Issue] <=DateAdd("m",-2,Date()),

and should be placed at the end of your SQL after the FROM line.

Two mistakes/potential problems. You got the syntax for DateAdd
backwards, and you referenced an alias. Access does not always like it
when you reference alias's, for purposes of criteria, sorting, etc. it
is safest to reference the original field, not the alias.






Hey guys,

If someone could spare the time in helping me with this problem it
would be very much appreciated...and apologies if it seems like a very
basic questions, however, I do not possess the experience and knowledge
like many of the people on here, and very much appreciate your
assistance.

I need to modify my SQL Statement to filter out all the records that I
am not interest in, bascially I need this query only showing records
that fall within a certain Date range...based on my "DueDate"
field......

The SQL Statement is as follows:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date],
ShipsInformation.EmailAddress
FROM ShipsInformation;

Now I understand that I need to add a "WHERE" criteria for the date
section....I want this query to only show records that fall within the
next two months of the "Due Date"....so something along the lines of :
WHERE DueDate <=DateAdd(Date,"m,",-2) ?


If someone could please explain how I could implement such a Query it
would be much appreciated...

Kind Regards


Liam.
Click to expand...
Click to expand...
Click to expand...
 
D

debra turner

in the query you built for the data. put this in the criteria

[enter start date]and[enter end date]
you will get only the data in those ranges

or you can do this for a specific date
in the criteria of the query enter this information
[enter date]
you will only get that date.
it will come out in the output report and do a pop up window to ask you to
enter the date.
whala!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

This is killing me! 2
DateAdd Calculation? 5
Date query 2
query on date 1
Query User Defined date range 2
Another most-recent date query 0
Dcount returning no results!!! 0
Syntax error on Date range query - Help! 5

Top