I have a table that I have listed all our warranty dates. I want to query my table to have it select the warranties that are going to expire during the month of March. So, I need it to list the school, the project & the date the warranty is going to expire. How do I write a query to make it produce this list? I want to redo the query each month, so I am guessing whatever the query looks like, I will just replace the date/month. Can someone help me. I have Access version 07 - 10 - haven't used Access since version 03. Thanks!

I can see two solutions to this problem:

1. You can use the Month() function in the query expression:
Though simple, this solution can be very slow and un-efficient if the table has many rows, because a call to a VBA function is made for each row of the table.

2. You can base your query on the SQL Between operator:
In this case, you can encounter a problem if the Time part of the Date was stored in the table (i.e. a non-zero value is stored in the Time part). You would then have to use:
or:
Both expressions yield the same result sets in most cases, if the Time part of the Date/Time value was systematically stored in the table.

Note: The format of a Date/Time expression in a query is fixed and does not depend on the Locale settings of the Control Pannel of Windows. It can be: "mm/dd/yyyy" (US) or the ISO format "yyyy-mm-dd", the query builder (if you use it), will automatically convert the ISO format to the US format.

Thanks so much. My table only deals with dates, no time. I have taken the two ways you listed and typed them exactly as you typed it into my query criteria. When I run query, I get aEnter Parameter Value box pop up that ask for Table.DateColumn. I'm sorry, but what are they asking for?

Month(Table.DateColumn) = 3


Run results:
Enter Parameter Value
Table.DateColumn



[Table].[DateColumn] Between #3/1/2013# And #3/31/2013#

Run results:
Enter Parameter Value
Table.DateColumn

Please post the SQL expression of the query: open it in SQL view and copy the text, then paste it here.

I won't have much free time within the coming days but I'll try to find a moment to adapt the code to your needs and post it back.

what you could do is make the query return, say ALL warranties expiring between the current date AND the end of next month, or say the month after that.
the advantage, there is no coding no parameters, its the same query

its all down to the where clause...
where Expirydate between date() and ....

the .... depends on how tightly you wish to define the end limit
no point int he end of this month.... to tight
its down to how you use the dattime functions, specifically dateadd (or datesub)

end of next month is (effecctively)
dateadd("M", 2, format(date(),"#YYYY/MM/01#"))
..this (should) work because we are setting the limit to
dateadd("M",2 ... adds two months to the sepcified date
..which is format(date(),"#YYYY/MM/01#"))
the format takes the current month and year but sets the day element to 01
which based ontodays date that will be 01 March 2013
so we ask for rows whose expiry date will be less than or equal to 01 March 2013
if you wanted the next 2 months
dateadd("M",3


OK so you dont want o1, you want one day before that
so either subtract a day off the
dateadd("d",-1,dateadd("M", 2, format(date(),"#YYYY/MM/01#")))

or
WHERE expiry date >= date() AND expiry date < dateadd("M", 2, format(date(),"#YYYY/MM/01#"))

SELECT [Warranties Table].[Warranty Expires On], [Warranties Table].[Warranty Expires On]
FROM [Warranties Table]
WHERE (([Table].[DateColumn] Between #3/1/2013# And #3/31/2013#))
ORDER BY [Warranties Table].[Warranty Expires On];

I opened my query in SQL, copied and pasted it above. I hope this helps in determining what my problem is...

Again, thank you so much for taking time out of your day to assist an Access Dummy.

shouldn't datecolumn be replaced with the name of the column containing the date value which looks like its [Warranties Table].[Warranty Expires On]

Thanks Healdem. This worked and gave me the dates in March. The only thing I need help now with is getting the Campus ID and Project Description information to also show up when I run the query. Meaning, I need the information to look something like this:

Campus ID Project Description Warranty Expires On

KMS Install new lockers 3/1/2013


The only thing showing up is the Warranty Expires On column and dates.

Hate to ask for more help, but I need to know what date goes with what campus and project. Thanks so much.

So add the required columns to the SELECT part of the query. Eg:-
SELECT my, comma, separated, column, list FROM mytable
WHERE column = somecriteria
ORDER BY another, column, list