Converting text to numbers in order to limit results by value and order results by year & month

[spencerrichman]

I’ve got a Query that I’m trying to show results only for the months from January through the two digit month number that the user inputs. I’d like the results sorted by year, and then by month (within each year). Right now my results sort by year, but (i) not by month, and (ii) are not limited to the month that the user inputs (in this example I typed in “07” – but you can see that results include dates of 08/30/2024 and 09/01/2024 and 10/31/2024, etc). These are the results:




I believe that the crux of the problem lies in the fact that my original data source has the Date stored as text. In the prior query (from which this query pulls data), I’ve used the Right and Left functions to pull the two digit month and the four digit year out of the date text field. But I assume that Access still treats these as text. Do I need to convert them to numeric values for this to work…and if so, how do I do that?

[pbaldy]

You haven't shown your SQL, but you can use the CDate() function on the text-date field to have it seen as a date instead of text. It will likely error if there are Null values in there, or invalid dates.