Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Control query date criteria from a table in Access

Posted on 2014-02-10
6
Medium Priority
?
616 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

610 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