Control query date criteria from a table in Access

Hi Experts

I have a database with a number of tables which contain various project data.

Based on these tables, I've created multiple queries for monthly reporting and use a criteria range in the data field to select the records I want, e.g. >=#1/01/2014# And <#1/02/2014#.

Every month I have been editing the queries with the new date ranges before I run a DoCmd.TransferSpreadsheet module to export the data into Excel and into named ranges.

I am aware that I can control the query date criteria from an unbound form field, however ideally I would like to be able to have a table instead that contains a StartDate and EndDate field which I can change the dates each month.

Is this possible and can someone please help me with this?

I have attached an example database to assist.

Thanks
darls15
Example.accdb
darls15Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
use a form with textboxes or combo box where you can select the dates.

use a criteria like this

where [datefield] between Forms!NameOfForm!StartDate and Forms!NameOfForm!endDate
0
 
Rey Obrero (Capricorn1)Commented:
what is the month criteria of your query, if the current month is February?

you can use a criteria like this

between dateserial(year(Now()),Month(Now())-1,1) and  dateserial(year(Now()),Month(Now()),2)
0
 
darls15Author Commented:
Hi Rey

Thanks for getting back to me. This criteria works well however, sometimes the reports are run late due to a number of reasons (staff absense etc). For example, it could be the month of June, however the reports need to be run for the month of March. This would still mean that I would have to manually update every query to produce the report.

Any suggestions?

Thanks
darls15
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
darls15Author Commented:
Hi again,

Thank you I've managed to place text boxes and enabled date pickers on them and this is now working with the queries.

Also would you know if it is possible to have the date fields on the form retain the selected dates when it is closed?

darls15
0
 
Rey Obrero (Capricorn1)Commented:
yes, you need to create a table with two fields (Start date, End Date) and make it the Record Source of the form.
0
 
darls15Author Commented:
got it! thank you very much, your assistance has been appreciated.
darls15
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.