Year-To-Date Query with a lime Twist

[AnneMarie]

Dear Access Community,

I have a database in which all entries are done on a day-to-day basis. A
summary report must be done that shows monthly figures as well as a one year
rolling sum. I have almost gotten everything figured out (thanks to all of
you!) except the report now has to be interactive so that a person can type
say the current month under my field "PlantDate" and have the query pull all
the records for the previous year. The problem is if the person types in
01/01/2007 the query does not pull the the last days of the month for 2007
and if they enter 01/31/2007 it doesn't pull the first of the month for 2006.

I have thought about doing the Start-End date but this is a program for
people who will not understand that you have to run from the first of the
month last year to the end of the month this year for a full "year"

Below is the query is have so far under my field "PlantDate" (Where [Input
Date] is input by the user so that it can be changed each month/quarter:
Between [Input Date] And DateAdd("m",-12,[Input Date])

Can someone give me a relatively easy solution along the lines of the query
above? Especially since I am not skilled in programming...I think it has to
do with DateSerial but I just can't seem to get it through my head.

Thank you again...
Anne-Marie

 

[Jeff Boyce]

AnneMarie

I agree with you, but perhaps for a different reason <g>!

Users don't understand about inclusive and exclusive dates, and probably
shouldn't have to! After all, they're only concerned about "last year",
right?

If this were mine, I'd first want the users to specify THEIR definition of
"last year". Does it mean every date in every month in the twelve months
preceeding the month we're currently in? Does it mean "start yesterday and
go back a full year"?

Once I had this info, I'd use the DateSerial() function to specific the date
range. For example, if users wanted all dates, all months preceeding the
current month, I'd use something like:

Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date()),Month(Date()),0)

That "0" in the second DateSerial() expression takes the zero-th day (the
first day, minus one) of the current month of the current year ... in other
words, the last day of the preceeding month.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AnneMarie]

Thank you for your reply. This report is for a state agency which mandates
the inclusive and exclusive dates and if it is wrong, there's a BIG fine
(which is why I can't depend on people typing in the correct date or even
looking at a calendar (which many of them won't do) to be sure that they
entered the 1st and last day of the months (I would love to tell them to
enter the month and year of the report period ie 02/2007 and let it fly). The
report has to include the first day of the month of the last year to the last
day of the month this year (02/01/06-02/28/07 OR 03/01/06-03/31/07) to show a
full rolling year.

My biggest problem has been allowing it to be an input so it can change
based on whether the user wants to see January, February, March or soon
April. When I copied and pasted your info into my query and replaced the
Date() parameter with input, it only showed 2006 data...not a range. What am
I doing wrong?

Between DateSerial(Year([Input Date])-1,Month([Input Date]),1) And
DateSerial(Year([Input Date]),Month([Input Date]),0)

If I can get this one thing done, I and my company will have this entire
reporting system DONE! Party!

Thank you!

AnneMarie

I agree with you, but perhaps for a different reason <g>!

Users don't understand about inclusive and exclusive dates, and probably
shouldn't have to! After all, they're only concerned about "last year",
right?

If this were mine, I'd first want the users to specify THEIR definition of
"last year". Does it mean every date in every month in the twelve months
preceeding the month we're currently in? Does it mean "start yesterday and
go back a full year"?

Once I had this info, I'd use the DateSerial() function to specific the date
range. For example, if users wanted all dates, all months preceeding the
current month, I'd use something like:

Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date()),Month(Date()),0)

That "0" in the second DateSerial() expression takes the zero-th day (the
first day, minus one) of the current month of the current year ... in other
words, the last day of the preceeding month.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear Access Community,

I have a database in which all entries are done on a day-to-day basis. A
summary report must be done that shows monthly figures as well as a one
year
rolling sum. I have almost gotten everything figured out (thanks to all of
you!) except the report now has to be interactive so that a person can
type
say the current month under my field "PlantDate" and have the query pull
all
the records for the previous year. The problem is if the person types in
01/01/2007 the query does not pull the the last days of the month for 2007
and if they enter 01/31/2007 it doesn't pull the first of the month for
2006.

I have thought about doing the Start-End date but this is a program for
people who will not understand that you have to run from the first of the
month last year to the end of the month this year for a full "year"

Below is the query is have so far under my field "PlantDate" (Where [Input
Date] is input by the user so that it can be changed each month/quarter:
Between [Input Date] And DateAdd("m",-12,[Input Date])

Can someone give me a relatively easy solution along the lines of the
query
above? Especially since I am not skilled in programming...I think it has
to
do with DateSerial but I just can't seem to get it through my head.

Thank you again...
Anne-Marie

 

[Jeff Boyce]

AnneMarie

Were this my project, I'd start substituting actual numbers in the
DateSerial() expressions to explore what it actually was doing, then convert
it back one step at a time to using the variable.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you for your reply. This report is for a state agency which mandates
the inclusive and exclusive dates and if it is wrong, there's a BIG fine
(which is why I can't depend on people typing in the correct date or even
looking at a calendar (which many of them won't do) to be sure that they
entered the 1st and last day of the months (I would love to tell them to
enter the month and year of the report period ie 02/2007 and let it fly).
The
report has to include the first day of the month of the last year to the
last
day of the month this year (02/01/06-02/28/07 OR 03/01/06-03/31/07) to
show a
full rolling year.

My biggest problem has been allowing it to be an input so it can change
based on whether the user wants to see January, February, March or soon
April. When I copied and pasted your info into my query and replaced the
Date() parameter with input, it only showed 2006 data...not a range. What
am
I doing wrong?

Between DateSerial(Year([Input Date])-1,Month([Input Date]),1) And
DateSerial(Year([Input Date]),Month([Input Date]),0)

If I can get this one thing done, I and my company will have this entire
reporting system DONE! Party!

Thank you!

AnneMarie

I agree with you, but perhaps for a different reason <g>!

Users don't understand about inclusive and exclusive dates, and probably
shouldn't have to! After all, they're only concerned about "last year",
right?

If this were mine, I'd first want the users to specify THEIR definition
of
"last year". Does it mean every date in every month in the twelve months
preceeding the month we're currently in? Does it mean "start yesterday
and
go back a full year"?

Once I had this info, I'd use the DateSerial() function to specific the
date
range. For example, if users wanted all dates, all months preceeding the
current month, I'd use something like:

Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date()),Month(Date()),0)

That "0" in the second DateSerial() expression takes the zero-th day (the
first day, minus one) of the current month of the current year ... in
other
words, the last day of the preceeding month.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear Access Community,

I have a database in which all entries are done on a day-to-day basis.
A
summary report must be done that shows monthly figures as well as a one
year
rolling sum. I have almost gotten everything figured out (thanks to all
of
you!) except the report now has to be interactive so that a person can
type
say the current month under my field "PlantDate" and have the query
pull
all
the records for the previous year. The problem is if the person types
in
01/01/2007 the query does not pull the the last days of the month for
2007
and if they enter 01/31/2007 it doesn't pull the first of the month for
2006.

I have thought about doing the Start-End date but this is a program for
people who will not understand that you have to run from the first of
the
month last year to the end of the month this year for a full "year"

Below is the query is have so far under my field "PlantDate" (Where
[Input
Date] is input by the user so that it can be changed each
month/quarter:
Between [Input Date] And DateAdd("m",-12,[Input Date])

Can someone give me a relatively easy solution along the lines of the
query
above? Especially since I am not skilled in programming...I think it
has
to
do with DateSerial but I just can't seem to get it through my head.

Thank you again...
Anne-Marie

 

[John Spencer]

It depends, but if the report is always for the current year and month,
you should be able to use

Between DateSerial(Year(Date())-1, [Enter ending Month Number] -1 ,1)
AND DateSerial(Year(Date()),[Enter ending Month Number] +1, 0)

If the report needs to be run for any year period then

Between DateSerial(Year([Enter End Date])-1,Month([Enter End Date])-1,1)
AND DateSerial(Year([Enter End Date]), Month([Enter End Date])+1,0)

HOWEVER, if you Date field contains a time component you would need to
modify the above slightly.

=DateSerial(Year([Enter End Date])-1,Month([Enter End Date])-1,1) AND

< DateSerial(Year([Enter End Date]), Month([Enter End Date])+1,1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you for your reply. This report is for a state agency which mandates
the inclusive and exclusive dates and if it is wrong, there's a BIG fine
(which is why I can't depend on people typing in the correct date or even
looking at a calendar (which many of them won't do) to be sure that they
entered the 1st and last day of the months (I would love to tell them to
enter the month and year of the report period ie 02/2007 and let it fly). The
report has to include the first day of the month of the last year to the last
day of the month this year (02/01/06-02/28/07 OR 03/01/06-03/31/07) to show a
full rolling year.

My biggest problem has been allowing it to be an input so it can change
based on whether the user wants to see January, February, March or soon
April. When I copied and pasted your info into my query and replaced the
Date() parameter with input, it only showed 2006 data...not a range. What am
I doing wrong?

Between DateSerial(Year([Input Date])-1,Month([Input Date]),1) And
DateSerial(Year([Input Date]),Month([Input Date]),0)

If I can get this one thing done, I and my company will have this entire
reporting system DONE! Party!

Thank you!

AnneMarie

I agree with you, but perhaps for a different reason <g>!

Users don't understand about inclusive and exclusive dates, and probably
shouldn't have to! After all, they're only concerned about "last year",
right?

If this were mine, I'd first want the users to specify THEIR definition of
"last year". Does it mean every date in every month in the twelve months
preceeding the month we're currently in? Does it mean "start yesterday and
go back a full year"?

Once I had this info, I'd use the DateSerial() function to specific the date
range. For example, if users wanted all dates, all months preceeding the
current month, I'd use something like:

Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date()),Month(Date()),0)

That "0" in the second DateSerial() expression takes the zero-th day (the
first day, minus one) of the current month of the current year ... in other
words, the last day of the preceeding month.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear Access Community,

I have a database in which all entries are done on a day-to-day basis. A
summary report must be done that shows monthly figures as well as a one
year
rolling sum. I have almost gotten everything figured out (thanks to all of
you!) except the report now has to be interactive so that a person can
type
say the current month under my field "PlantDate" and have the query pull
all
the records for the previous year. The problem is if the person types in
01/01/2007 the query does not pull the the last days of the month for 2007
and if they enter 01/31/2007 it doesn't pull the first of the month for
2006.

I have thought about doing the Start-End date but this is a program for
people who will not understand that you have to run from the first of the
month last year to the end of the month this year for a full "year"

Below is the query is have so far under my field "PlantDate" (Where [Input
Date] is input by the user so that it can be changed each month/quarter:
Between [Input Date] And DateAdd("m",-12,[Input Date])

Can someone give me a relatively easy solution along the lines of the
query
above? Especially since I am not skilled in programming...I think it has
to
do with DateSerial but I just can't seem to get it through my head.

Thank you again...
Anne-Marie

 

[Michael Gramelspacher]

Thank you for your reply. This report is for a state agency which mandates
the inclusive and exclusive dates and if it is wrong, there's a BIG fine
(which is why I can't depend on people typing in the correct date or even
looking at a calendar (which many of them won't do) to be sure that they
entered the 1st and last day of the months (I would love to tell them to
enter the month and year of the report period ie 02/2007 and let it fly). The
report has to include the first day of the month of the last year to the last
day of the month this year (02/01/06-02/28/07 OR 03/01/06-03/31/07) to show a
full rolling year.

My biggest problem has been allowing it to be an input so it can change
based on whether the user wants to see January, February, March or soon
April. When I copied and pasted your info into my query and replaced the
Date() parameter with input, it only showed 2006 data...not a range. What am
I doing wrong?

Between DateSerial(Year([Input Date])-1,Month([Input Date]),1) And
DateSerial(Year([Input Date]),Month([Input Date]),0)

If I can get this one thing done, I and my company will have this entire
reporting system DONE! Party!

Thank you!

AnneMarie

I agree with you, but perhaps for a different reason <g>!

Users don't understand about inclusive and exclusive dates, and probably
shouldn't have to! After all, they're only concerned about "last year",
right?

If this were mine, I'd first want the users to specify THEIR definition of
"last year". Does it mean every date in every month in the twelve months
preceeding the month we're currently in? Does it mean "start yesterday and
go back a full year"?

Once I had this info, I'd use the DateSerial() function to specific the date
range. For example, if users wanted all dates, all months preceeding the
current month, I'd use something like:

Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date()),Month(Date()),0)

That "0" in the second DateSerial() expression takes the zero-th day (the
first day, minus one) of the current month of the current year ... in other
words, the last day of the preceeding month.

In addition to DateSerial this might work:

BETWEEN DATEADD("m",DATEDIFF("m",2,[Enter Date:])-11,2)
AND DATEADD("m", DATEDIFF("m",1,[Enter Date:]),1)

The 2 is base date 1/1/1900, the first day of the month.
The 1 is base date 12/31/1899, the last day of the month.

So between 11 months prior to the first day of current month
and the last day of current month.

Run in Immediate window:

?DATEADD("m",DATEDIFF("m",2,now)-11,2)
5/1/2006
?DATEADD("m",DATEDIFF("m",1,now),1)
4/30/2007