Solved

Control query date criteria from a table in Access

Posted on 2014-02-10
6
609 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
  • 3
  • 3
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 119

Expert Comment

by:Rey Obrero
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now