If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Year-To-Date Query with a lime Twist
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 |
#2
|
|||
|
|||
Year-To-Date Query with a lime Twist
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" wrote in message ... 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 |
#3
|
|||
|
|||
Year-To-Date Query with a lime Twist
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! "Jeff Boyce" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
Year-To-Date Query with a lime Twist
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 "AnneMarie" wrote in message ... 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! "Jeff Boyce" wrote: 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" wrote in message ... 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 |
#5
|
|||
|
|||
Year-To-Date Query with a lime Twist
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 '================================================= === AnneMarie wrote: 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! "Jeff Boyce" wrote: 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" wrote in message ... 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 |
#6
|
|||
|
|||
Year-To-Date Query with a lime Twist
|
Thread Tools | |
Display Modes | |
|
|