Prompting for a parameter in a query when one isnt present

[Joe Morris]

I am trying to use an Iif statement in a query, using a calculated field as
one of the result paramets:

Exp1: Iif(

="9002,[tothrs],0)

[code] is a field in the query being drawn from a table, while [tothrs] is a
calculated field in the same query.  But for some reason it prompts me for
[tothrs] when I run the query.

This seems to happen anytime I use a calculated field in a query in another
calculation?  Is this not allowed in Access and if so how do I get around
this?

Any suggestions

 

[John Spencer]

You need to redo the calculation that is TotHrs in the expression.

Your other choice is to use nested queries. That is you do the calculation
for TotHrs in a saved query and then use that saved query as the source for
another query. If you do that then TotHrs will be available to be used in
your Expression.
For example if TotHrs was FldX * FldY then your expression becomes
Exp1: IIF(

="9002",FldX*FldY,0)

SOMETIMES Access will allow you to do what you want.  It will always fail if
you try to filter or sort by the expression you are building that uses a prior
expression.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

Joe -

Two work-arounds:
1 - Replace [tothrs] with the calculation.
2 - Don't put the calc in this query, but create a query based on this
query. There you can use the [tothrs] in the Iif statement.