Update Criteria for Yearly Date Range Without User Input

Question

I want to apply a date range as criteria for a query that isn't the standard current year, but avoid the necessity of user input.

I have a form in which the user inputs their billable time, and I have a field on the form where I want to display their current total billable hours for the period. The period runs from December 1st to November 30th of the following year. Once the current period ends, I want the calculation to automatically start over for the next new period (on December 1st).

I've reviewed all the various date calculation formats, but I'm stumped on how to get the period to update as it would if I were just trying to use the current year. I'm an "as needed" Access implementer, so for this I need some expert advice.

Thank you in advance for your responses.

Answer 1

Calculate range years based on current date with an IIf(). Consider:

SELECT * FROM sometable
WHERE datefield
BETWEEN DateSerial(Year(Date()) + IIf(Month(Date())=12, 0, -1), 12, 1) 
AND DateSerial(Year(Date()) + IIf(Month(Date())=12, 1, 0), 11, 30);

UPDATE: Gustav has provided a more compact calculation, however, the above expressions could be useful in query or textbox to display date range in a report header.

Answer 2

Offset both date values by one month, then you can filter by the year:

Select * From YourTable
Where DateDiff("yyyy", DateAdd("m", 1, Date()), DateAdd("m", 1, [YourDateField])) = 0