Query formulas (dates in formulas to be changed only once)

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I have several queries that contain several hard-coded formulas. If
possible, I would prefer that the formulas be referenced in a control or
setup table. Does that make sense? Instead of going through my queries each
month and updating hardcoded dates, I would like the dates to be changed only
once and have all the formulas reference this source.

Thank you!!

Craig
 
It makes sense to do this. How you do it depends on what the formulas are?

For instance if you are just trying to get the data for a prior month, you
can change the formula to get the prior month based on the system date.
To get all records for the prior month you might use something like the
following (assumption here is that the field doesn't contain a time
component).
Criteria: Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)

If the dates are not necessarily related to the current date, you can store
the dates in a single record table and then use that table in your query or
use DLookup function to get the date values.

Or Open a hidden form with the table as the source and controls bound to
each field.

Then you can refer to the various controls in your queries.

Criteria: Between Forms!MyDateForm!txtFieldStartDate and
Forms!MyDateForm!txtFieldEndDate



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top