Criteria selection

  • Thread starter Thread starter Robert Raley
  • Start date Start date
R

Robert Raley

MS Access 2003

I am using a query to select records between a date Range. This is the
criteria that I am using

between # 9/16/2005 # AND # 9/30/2005 #

I have a table named Test with two fields named start and finish.

I would like to use the dates that are in the table fields to use in the
criteria so that I do not have to edit the query each time. Just update the
table.

Many thanks

Bob Raley
 
Dear Robert:

I'm going to assume there's some column I'll call "TestKey" and another
column "TestValue" in the Test table.

I'll also assume there are text values in TestKey, say "Start Date" and "End
Date"

So that these queries return those values:

SELECT TestValue FROM Test WHERE TestKey = "Start Date"
SELECT TestValue FROM Test WHERE TestKey = "End Date"

Now, within your query, you can reference these values as Sub-Queries!
Where you now have:

# 9/16/2005 #

replace that with

(SELECT TestValue FROM Test WHERE TestKey = "Start Date")

Don't omit the parens around this. Do a similar thing with the End Date.

You need to correct the column names and key values I used with the correct
column names and values you have used.

If you have any problem implementing this, please send me the 3 separate
queries.

1) the query like I show above to return the Start Date.
2) the query like I show above to return the End Date.
3) the complete SQL of your query with the "between # 9/16/2005 # AND #
9/30/2005 #"

I should then be able to put these pieces together.

Tom Ellison
 
Back
Top