Use a Table as a configuration/Setup table?

[pgoulet]

Frequently, I am asked to change the starting and ending dates for a report. There are several locations in several cascading querys that require modification.

A sample of the working model is:
between #9/26/05# and #10/30/05#

What I am looking for is something like ---
"Between #&"[tbl_Setup].[startdate]&"# and #"&[tbl_Setup].[endDate]&"#"

Admittedly, I do get a type mismatch error when I attempt this.

Any ideas.

Thanks Paul

 

[PHV]

If you have a table (tbl_Setup) with only one row then you may simply add it in the FROM clause (cartesian product) and use its columns in the WHERE clause:
SELECT ...
FROM tbl_Setup, yourTable INNER JOIN anotherTable ...
WHERE SomeField Between tbl_Setup.startdate And tbl_Setup.endDate



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rubbernilly]

Look at CDate and DLookup.

I use a table called "ProgramData" that has fields of:

ID (AutoNumber)(Primary Key)
Type
Value1
Value2
etc.

So you could have a Type of "Date Spread", with Value1 being the place of use, Value2 being the Start Date, and Value3 being the End Date.

Date Spread
YourForm
10/1/2005
10/30/2005

Then use Dlookup to get the value, and convert it to Date format. Dlookup will only get the first, so keep each entry unique. It will also return a string, so you'll have to convert it.

 

[pgoulet]

Thanks for the input. My plan is to use a table of just one record.

Thanks again.