Year to date/ End Date Form

[AJordan]

I am trying have a calculated field which gives me the year to date amount of orders. I currently have a query which is set up to return the amount of orders Between user defined dates

Example 
Between [forms]![fmr1]![txtbegdate] AND [forms]![fmr1]![txtenddate]

Now this has worked fine but now I am interested in being able to know year to date information based on ([forms]![fmr1]![txtenddate] - 365)
What is the correct criteria for this?

 

[Brianwarnock]

I'm a little confused as to what you are actually trying to do, does the start date not come into this? Incidentally to subtract a year I would use Dateadd

Brian

 

[boblarson]

now I am interested in being able to know year to date information based on ([forms]![fmr1]![txtenddate] - 365)
What is the correct criteria for this?

Actually the value of end date -365 wouldn't necessarily give you a year to date. If you really want a Year to Date then you would more likely want criteria of this:

Between DateSerial(Year(Date()), 1, 1) And Date()

Which would get you everything between January 1st of the current year and up to, and including, today.

 

[boblarson]

If you want a ROLLING year then the DateAdd function like Brian suggested would be what you would use.

 

[AJordan]

Actually the value of end date -365 wouldn't necessarily give you a year to date. If you really want a Year to Date then you would more likely want criteria of this:

Between DateSerial(Year(Date()), 1, 1) And Date()

Which would get you everything between January 1st of the current year and up to, and including, today.

Yes this is what I intend to do, I really dont know why I did the "-365" as an example. My only question would be if I could replace Date(), with a user defined date from a form.

The issue occurs when a user decides they want to do a monthly report for "April", but the run the report on May 5th. So my main concern is a user defined end date. Would this work?

Between DateSerial(Year(Date()), 1, 1) And [forms]![frmX]![txtEndDate]

 

[Brianwarnock]

yes it would give the data from the start of the year to the date on the form.

Brian

 

[AJordan]

OK, thanks guys for your help. I will try this a little later to see if everyting goes smoothly