Criteria in Calculated Field of a Query

[FrankMars]

I created a calculated field in my query. On the criteria line, I wrote "Between Nz([Forms]![zzfaCriteria]![tbxRateFrom]) And [Forms]![zzfaCriteria]![tbxRateTo]". When I run the query, a message box comes up asking for a parameter value of a field which is a component of the formula in the calculated field. Is it possible to get the range I'm seeking without the message box coming up?

 

[CindyK]

Frank - the Nz function requires two arguments. If you drop the nz all will be well IF you have dates in both fields. If you want the user to be able to leave the first date blank, you might do something like this:

Between Nz([Forms]![zzfaCriteria]![tbxRateFrom],#1/1/1900#) And [Forms]![zzfaCriteria]![tbxRateTo]

which will pull all dates from 1/1/1900 up through the date entered in the RateTo box. (I'm assuming you're using dates - if the rate is a number field, then replace #1/1/1900# with a zero!

Cindy

 

[FrankMars]

Cindy - thanks - that worked on some fields but not on another. The field that it didnt work on is a calculated field with an additional calculated field contained in its formula. When I run the query, a message box comes up asking for a parameter value for the calculated field which is a component of the formula.

For example;

Problem Field: tbxCurrPricePerSF: [Curr/Sale Price]/[tbxSFcalc]

Problem Criteria: Between Nz([Forms]![zzfaCriteria]![tbxRateFrom],0) And Nz([Forms]![zzfaCriteria]![tbxRateTo],9999999)

Message Box "Enter Parameter Value" Field: tbxSFcalc: IIf([SF]=1,[tbxAutoCalc]*1.085,[SF])

Im trying to run this query without the message box coming up - is this possible?

 

[PHV]

tbxCurrPricePerSF: [Curr/Sale Price]/IIf([SF]=1,[tbxAutoCalc]*1.085,[SF])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886