By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

Year expression?

P: 1
MS Access 2003

Final goal:
It display the past two full years and current year totals in a crosstab as 2010, 2011, 2012. Next year it should be 2011, 2012 full years, and YTD of 2013 and so on.

2010 & 2011 will be full year's totals and the current year will be year to date (YTD) totals.

First I tried this to get the past two years and to date: Between DateAdd("y",2,([TransactionDate])) And Date()
But I am not getting a result.

Not sure if that's the way to go or have three separate columns in a query to show PriorYear (2010), LastYear (2011), and YTD (Year to date).
If so what are the correct expressions?

Thank you very kindly.
Mar 9 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 12,366
I'm not sure why you're adding two years to the transaction date. It should be between the current year minus two and the current year.
Mar 9 '12 #2

Expert Mod 15k+
P: 31,494
For the filtering try something like :
Expand|Select|Wrap|Line Numbers
  1. [DateField] >= CDate("1/1/" & Year(Date()) - 2)
[PriorYear] is any value of [DateField] where Year([DateField]) = Year(Date()) - 2.
[LastYear] is any value of [DateField] where Year([DateField]) = Year(Date()) - 1.
[YTD] is any value of [DateField] where Year([DateField]) = Year(Date()).

It only made more sense to me because I'd read and deleted the other question posted along similar lines :-(
Mar 9 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.