YTD Query

[jasonman]

Our fiscal year starts April 1. So each YTD query has a start date of April 1. Problem is if a query is run in Jan then DateSerial(Year(Date()),4,1) does not work. How can I adjust the query below to make sure if then query is run in Jan it gets 4/1 of the previous year and if the query is run in Nov it gets 4/1 of that year? Please help. Will I need to use a IFF statement in the query. Please help. Thanks.



If today is 11/6/2010 then the query below sums cart fee values between 4/1/2010 and 11/6/2010, as well as 4/1/2009 and 11/6/2009. I do this because the query controls a graph which compares this year to last year.

SELECT DISTINCT

(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()),4,1) And Date()))) AS Expr1,

(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()) -1,4,1) And Date()-365))) AS Expr2
FROM [GOLF SHOP OPERATIONS];

To simplify, if I run a query using
WHERE ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()),4,1) And Date()))
in November then the result will be correct because it sums all values between 4/1 and the present day.
However I think that when I run that same query in Jan the DateSerial(Year(Date()),4,1) will get 4/1/ of that year, when I need 4/1 of the previous year. So it will try to sum data between 4/1 and 1/5 of the same year, which won't work. Thanks again.

[pbaldy]

Try

Year(Date()) - IIf(Month(Date()) < 4, 1, 0)

[jasonman]

Hey thank you!!!

Does the query below look correct?

It looks as if its working.

SELECT [GOLF SHOP OPERATIONS].TheDate
FROM [GOLF SHOP OPERATIONS]
WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date()),4,1) And Year(Date()) - IIf(Month(Date()) < 4, 1, 0)

BUT HOW.... I need to understand. Please tell me the language behind the query above.

[jasonman]

Also, the date range being returned includes 4/1/2010. Can I adjust the query just so that only 4/1/2009 - 3/31/2010 for example are returned. Thanks Again.

[pbaldy]

I get a free round, right?

That doesn't look like I would have expected. Try:

WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date()) - IIf(Month(Date()) < 4, 1, 0),4,1) And Date()

Basically what it's doing is, within the DateSerial function for the Year argument, it's saying "if the current month is less than 4, subtract 1 from the current year". You can do something similar with the second date if you want it to run to 3/31 instead of the current date.

[jasonman]

Thanks. Looks like I wanted to exactly! If your ever near the Queen City, u can have a round on me.

[pbaldy]

Happy to help. I'm not familiar with the Queen City, so I guess I've never been near it.

[jasonman]

Please understand my pain, the Project hangs on these final solutions....now whats happening is as mentioned above the second set of data needs to return 4/1/2008 to Date()-365 if today's date is In jan of 2010. I hope I'm explaining that right. The query returns two YTD sums, one for that current year and one for the previous year. But since our fiscal starts 4/1 then if the query is run in Jan the previous year now actually started two years ago.

My query before is written like this,

HAVING ((([GOLF SHOP OPERATIONS].TheDate) Between DateSerial(Year(Date()) -1,4,1) And Date()-365))) AS SoftGoodsTwo

So if today is Nov 8, 2010 it returns YTD for sums for 4/1/2009-11/8/2009, Perfect! But it won't work next Jan. Run in Jan of 2010 it should return 4/1/2008 to Jan 2009. Please help one more time. Thanks again.

[pbaldy]

Am I missing something? The same technique but subtracting 2 and 1 instead of 1 and 0 would work, would it not?

[jasonman]

Thanks for walking me through that... So my final solution looks like

SELECT DISTINCT

(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And Date()) AS Expr1,

(SELECT Sum([GOLF SHOP OPERATIONS].CartFees)
FROM [GOLF SHOP OPERATIONS]
WHERE [GOLF SHOP OPERATIONS].TheDate Between DateSerial(Year(Date()) - IIf(Month(Date()) < 4, 2, 1),4,1) And Date()-365) As Expr2
FROM [GOLF SHOP OPERATIONS];

Thanks again.

[pbaldy]

Happy to help, and hit 'em long and straight!

By the way, you might want to use DateAdd() to subtract a year rather than use Date()-365. It would be a subtle problem, but around leap years you can end up a day off. Not sure about what precision you want there. In other words:

?dateadd("yyyy", -1,#3/1/2012#)
03/01/2011
?#3/1/2012# - 365
03/02/2011