Year to date for last three years

[unclefink]

I am trying to build a query to show year to date case counts over a period of three years.

The start date of course being the first day for each year and the ending date or "to date" would be "Date()".

I'm not exactly sure how to even start building this in addition; howe do i make "Date()" work if im attempting to search criteria in a different year?

Any suggestions. I imagine this is a "Duh" moment as soon as suggestions are given; but I cant even picture a starting point.

 

[Alansidman]

Criteria:
Current Year would be Between #1/1/2013# and Date()
Last year would be Between #1/1/2012# and DateAdd("yyyy",-1,Date())
Two years back would be Between #1/1/2011# and DateAdd("yyyy",-2,Date())

 

[unclefink]

Criteria:
Current Year would be Between #1/1/2013# and Date()
Last year would be Between #1/1/2012# and DateAdd("yyyy",-1,Date())
Two years back would be Between #1/1/2011# and DateAdd("yyyy",-2,Date())

Is there something I can replace the start dates with so I dont have to go in every year to replace the starting date criteria? Something to make the year of the start date default to that year?

 

[Alansidman]

Use the Year function, the Date function and the DateAdd function.

Year(Date()) for the current year
Year(Dateadd("yyyy",-1,Date()) for one year back, etc.

Or instead of hard coding the criteria, you could use a search form allowing your users to input the applicable start date in a form. Look at this tutorial on how to set up a search form and adapt it to your needs.

http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[nanscombe]

What I have done in the past is to set up the functions below and placed them into a module so I can use in queries.

Public Function yearCurrent()
  yearCurrent = Year(Now)
End Function

Public Function yearLast()
  yearLast = Year(Now) - 1
End Function

Public Function yearLast2()
  yearLast2 = Year(Now) - 2
End Function

Public Function monthCurrent()
  monthCurrent = Month(Now)
End Function

I then added a couple of fields to my queries theYear: Year([myQueryDate]), theMonth: Month([myQueryDate]).

You may need to use the NZ() function in some way if you have any blank dates in your data.

I could then use the following criteria:
theYear In(yearCurrent(), yearLast(), yearLast2()) [Gives this year, last year and previous year]
theMonth <= monthCurrent() [Gives year to date]

Or, if you don't want the current month included ..
theMonth < monthCurrent() [Gives year to date]


If you didn't want to write any functions you could simply use:

theYear: Year([myQueryDate]) as an extra query Column
In(Year(Now), Year(Now) - 1, Year(Now) - 2) as the criteria

theMonth: Month([myQueryDate]) as an extra query Column
<= Month(Now) as the criteria, including this month
< Month(Now) as the criteria, excluding this month

 

[Brianwarnock]

I presume that you want to go from the start of the year to today, whenever that is and ditto for the two previous years. The simple way is with DateSerial

Between dateserial(year(date),1,1) and date
Between dateserial(year(date)-1,1,1) and dateserial(year(date)-1,month(date),day(date))

Etc

Brian

 

[unclefink]

I tried to "Thank" you on your last post; however for some reason it would not work, kept forwarding me to a secondary page indicating I could not do what I wa trying to do because I wasnt logged in. In any case, I am in fact logged in and would like to thank you for the reccomendation. What you suggested is exactly what I needed for this specific task, and it works ask needed.