iif in Criteria line

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

I have a query that drives my report. This query returns data base on a
criteria. The Criteria needs to change based on the ProductCode. If the
ProductCode is "Flush" then I don't want any criteria (All Flush records are
returned), but if the ProductCode is anything else, then use the Criteria.

Here is what I have, but it is not working.

Thanks again for your help,

Brad

iif( [tblFeedRecords]![ProductCode] <>"Flush",Between
[Forms]![frmRPTGenerator]![StartDate] And
[Forms]![frmRPTGenerator]![EndDate],Like"*")
 
You can't place the "how to compare" inside the IIf(). You can only place
the values to compare to in the IIf(). Maybe something like this would work:

Between IIf([tblFeedRecords]![ProductCode]
<>"Flush",[Forms]![frmRPTGenerator]![StartDate], [YourDateField]) AND
IIf([tblFeedRecords]![ProductCode]
<>"Flush",[Forms]![frmRPTGenerator]![EndDate], [YourDateField])
 
Brad said:
Thanks for taking the time to read my question.

I have a query that drives my report. This query returns data base on a
criteria. The Criteria needs to change based on the ProductCode. If the
ProductCode is "Flush" then I don't want any criteria (All Flush records are
returned), but if the ProductCode is anything else, then use the Criteria.

Here is what I have, but it is not working.

iif( [tblFeedRecords]![ProductCode] <>"Flush",Between
[Forms]![frmRPTGenerator]![StartDate] And
[Forms]![frmRPTGenerator]![EndDate],Like"*")
Click to expand...


You can not include the comparison operator in the Iff. You
have to use complete expressions in both the true and false
part of the IIf:

iif([tblFeedRecords].[ProductCode] <>"Flush", datefield
Between [Forms]![frmRPTGenerator]![StartDate] And
[Forms]![frmRPTGenerator]![EndDate], True)

You may have trouble placing that in a field's criteria
line. If you do, then use the IIf expression in a new
calculated field and use the criteria True.
 
Back
Top