Max of multiple fields

[John]

I have a report that returns four periods (quarters) of data. Some of the
responses are a modified Yes/No rather than numerator denominator. Available
responses to a yes/no question are YES (-1 value), No (1), or N/A(0) - I
built a lookup table rather than use the built in Yes/no.

If any quarter has a "No" (1) response, then I want the YTD field (tbxYTD)
of the report to equal "No". If there is one or more "Yes" responses during
the quarters, the tbxYTD should be "YES". If they are ALL "N/A" tbxYTD
should be "N/A". My approach is to have a record source of the tbxYTD be
something like; If Max of Q1, Q2, Q3, Q4 >0 then "No", otherwise if Min of
Q1, Q2, Q3, Q4 <0,"Yes", otherwise "N/A"

Since I am an Excel convert here is what I would do in Excel. Can someone
translate it into syntax for Access?
If(Max(Q1, Q2, Q3, Q4)>0,"No",If(Min(Q1,Q2,Q3,Q4)<0,"Yes","N/A"))

 

[John Spencer]

I believe that the following will work for you in this instance.

IIF(1 in (Q1,Q2,Q3,Q4),"No", IIF(-1 in (Q1,Q2,Q3,Q4),"Yes","N/A"))

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