?
Solved

Control query date criteria from a table in Access

Posted on 2014-02-10
6
Medium Priority
?
615 Views
Last Modified: 2014-02-10
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
0
Comment
Question by:darls15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39848951
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
 

Author Comment

by:darls15
ID: 39848967
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39848983
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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:darls15
ID: 39849142
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39849209
yes, you need to create a table with two fields (Start date, End Date) and make it the Record Source of the form.
0
 

Author Comment

by:darls15
ID: 39849223
got it! thank you very much, your assistance has been appreciated.
darls15
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
New style of hardware planning for Microsoft Exchange server.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question