asking for parameter

[sudo]

I have a query with some fields that are directly from tables and other fields that are calculated fields (no big deal here). Some of the calculated fields refer to table fields in their formulae and other calculated fields refer to calculated fields in their formulae (still no big whoop). Some of the table fields have 1 selection criterion.

When I run the query described above, it works fine.

Here's where my woes begin. When I try to add a selection criteria to one of the calculated fields that refers to two calculated fields in its formula, all of a sudden, Access gives me 2 parameter prompts, one for each of the two calculated fields referred to in the formula. Without the selection criterion, there is no prompt for these values. I can't understand why adding a selection criterion all of a sudden confuses Access so that it doesn't "see" the values in the calculated fields referred to in the formula.

I don't know if this makes a difference, but the calculations all involve dates (some calculations result in a date, some in a number (DateDiff)).

I tried putting selection criteria in the calculated fields referred to in my target calculated field (thinking that Access has an order to the fields based on selection criteria rather than left to right order in the query), but that didn't make any difference.

The selection criterion I am trying to apply is:
>3
for a DateDiff calculation. I also tried using the criterion
In(4, 5, 6, 7)
with no joy. Both types of criteria result in the paramter prompts.

I am flumoxed, perplexed and baffled.

 

[pbaldy]

Try a second query based on the first, with the criteria in the second.

 

[sudo]

Thanks pbaldy.

That worked a treat. I've never done a query on a query, so that's a handy thing to know to do. I suspect I'll be using it a lot as Access is slightly retarded.

Thanks