Equal to or before today's date regardless of year

[Blacksmith7]

I am trying to do an access query that will give me the month and day that is equal to or less than today regardless of the previous year. For example; I have an Order date field with orders dating back 3 years. I want all orders that were made on or before February 2nd (because that's today's date) of the year it was made. If I run it tomorrow; it should be February 3rd and the next day the 4th, etc.

[davegri]

If it's before today's date it will select any date before today. Something like

OrderDate <= Date()

Date() will always be today's date.

[June7]

I am somewhat confused by: "on or before February 2nd (because that's today's date) of the year it was made". So you don't want records dated Feb 3 thru Dec 31 of each prior year?

[CJ_London]

always helps to provide some example data and the output required from that example data.

do you mean you want comparative year to date values with previous years?

[ArviLaanemets]

SELECT * FROM YourTable WHERE Month(YourDate) < Month(Date()) OR (Month(YourDate) = Month(Date()) AND Day(YourDate) <= Day(Date()))

[Blacksmith7]

Thank you everyone for the responses. I will try to clarify what I am trying to accomplish. In the sample below the cycle is representative of a year beginning July 1.
So Fall 2017 would represent orders placed between July 1 2016 - July 1 2017. I want the query to show all orders placed ON or BEFORE today's DAY regardless of the year. I want to see who placed an order ON or BEFORE February 8 or whatever today's DAY is.

Client	Order	Cycle	Order Date
John	312	Fall 2017	9/2/2016
Jane	498	Fall2018	10/18/2017
Sherry	213	Fall 2019	12/13/2018
Tyler	119	Fall 2017	2/19/2017

[CJ_London]

so what has the cycle got to do with it?All those dates are before today so they would all be reported.

What result would you expect from your example data?

[Blacksmith7]

I would expect the first three clients (John, Jane and Sherry) to show in the result.

[CJ_London]

Your logic is a complete mystery to me - 19th Feb 2017 is before today (8th Feb 2020) so how is that different? I understand perhaps you would exclude because 19th Feb is greater than 8th Feb (today) - but how do you decide to include 2nd Sept which is also greater then 8th Feb? - is it because it is 2nd September is greater than 1st July?

[Blacksmith7]

Yes. I am trying to make a comparison to date (DAY of year). So I want to see all orders placed on or prior to 8th Feb of the cycle the order was placed. Another way of saying it I guess would be to see how many orders were placed between July 1 and Feb 8 (today's DAY) for each cycle. Example: Fall 2017 cycle would be July 1 2016 - Feb 8 2017, Fall 2018 cycle would be July 1 2017 - Feb 8 2018, etc. Does this help? I apologize for not explaining it well but hopefully am moving in the right direction in better explaining.

[orange]

Ajax asked so what has the cycle got to do with it?
Seems it is critical, but still not clear.

Can you - in point form- describe how John, Jane and Sherry get selected, and how Tyler gets rejected(not selected)?

Yes we're moving in the right direction, but another step or two....

[Blacksmith7]

John, Jane and Sherry got selected because their Order Date was BEFORE today's DAY (February 8) within their cycle. Tyler was left off because his Order Date was AFTER today's DAY (February 8) within his cycle which is July 1 2016 - July 1 2017. Does this help?

[June7]

So the answer to question in post 3 is basically "Yes". Only instead of Jan - Dec use Jul - Jun as year = a fiscal year. So if current date is Feb 8 you want records from each fiscal year between Jul 1 yyyy - Feb 8 yyyy+1. This means Tyler is the only record excluded.

Crossing calendar years does complicate. Consider:

SELECT Orders.*
FROM Orders
WHERE ((([OrderDate] Between DateSerial(Year([OrderDate])-IIf(Month([OrderDate])<7,1,0),7,1) And DateSerial(Year([OrderDate])+IIf(Month([OrderDate])>=7,1,0),Month(Date()),Day(Date())))=True));

[Blacksmith7]

Ok. I will give this a shot later and let you know

[orange]

John, Jane and Sherry got selected because their Order Date was BEFORE today's DAY (February 8) within their cycle. Tyler was left off because his Order Date was AFTER today's DAY (February 8) within his cycle which is July 1 2016 - July 1 2017. Does this help?

Yes, Cycle is critical. And I see that June has posted with reference to Fiscal Year(your cycle). I think the mystery has been resolved. Interesting how communication evolves as facts and terminology progress.