Using IIF in Report

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

Guest

Hi all,
I have built a Report in Access 2003 that calculates quite a lot of fields,
two of which calculate and return text values "High" or "Low" as their answer.
Then there is another field "Classification" that, in turn, uses the above
two (calculated) fields -"CatGP" and "CatPOP" for its own calculation ... In
Excel the expression is as follows:-

=IF(AND(N11="High",O11="High"),"Star",IF(AND(N11="Low",O11="High"),"Plowhorse",IF(AND(N11="High",O11="Low"),"Puzzle",IF(AND(N11="Low",O11="Low"),"Dog",""))))

Naturally in the Access Report the "Cell references" would be replaced with
the two fields -"CatGP" and "CatPOP".

I have tried the above Excel formula and replaced "IF" with "IIF" and the
Cell references with the two fields as above. But I cannot get it to work.

If I use ONLY the following 'formula'
=IIf([CatGP]="High",IIf([CatPOP]="High","Star"),"")
it does actually evaluate to "Star" where both "CatGP" and "CatPOP" have a
value of "High".

Could you PLEASE help by showing me what I should be doing for the field
"Classification" to evaluate the following,

=IIf ([CatGP]="High",IIf([CatPOP]="High","Star"),
=IIf ([CatGP]="Low",IIf([CatPOP]="High","Plowhorse"),
=IIf ([CatGP]="High",IIf([CatPOP]="Low","Puzzle"),
=IIf ([CatGP]="Low",IIf([CatPOP]="Low","Dog"),""))))

Thanks in advance, all the best for the Holiday season.
hotcat
 
hotcat said:
I have built a Report in Access 2003 that calculates quite a lot of fields,
two of which calculate and return text values "High" or "Low" as their answer.
Then there is another field "Classification" that, in turn, uses the above
two (calculated) fields -"CatGP" and "CatPOP" for its own calculation ... In
Excel the expression is as follows:-

=IF(AND(N11="High",O11="High"),"Star",IF(AND(N11="Low",O11="High"),"Plowhorse",IF(AND(N11="High",O11="Low"),"Puzzle",IF(AND(N11="Low",O11="Low"),"Dog",""))))

Naturally in the Access Report the "Cell references" would be replaced with
the two fields -"CatGP" and "CatPOP".

I have tried the above Excel formula and replaced "IF" with "IIF" and the
Cell references with the two fields as above. But I cannot get it to work.

If I use ONLY the following 'formula'
=IIf([CatGP]="High",IIf([CatPOP]="High","Star"),"")
it does actually evaluate to "Star" where both "CatGP" and "CatPOP" have a
value of "High".

Could you PLEASE help by showing me what I should be doing for the field
"Classification" to evaluate the following,

=IIf ([CatGP]="High",IIf([CatPOP]="High","Star"),
=IIf ([CatGP]="Low",IIf([CatPOP]="High","Plowhorse"),
=IIf ([CatGP]="High",IIf([CatPOP]="Low","Puzzle"),
=IIf ([CatGP]="Low",IIf([CatPOP]="Low","Dog"),""))))
Click to expand...


Actually, that looks like it might work if you just tool out
all but the first = sign.

OTOH, it could be shortened somewhat to:

=IIf([CatGP]="High",IIf([CatPOP]="High","Star","Puzzle"),
IIf([CatPOP]="High","Plowhorse","Dog"))

OTOOH, you would probably be better off over the long haul
if you put all that info into a "lookup" table and retrieved
the classification from there.

Table Classifications:
GP: Text
POP: Text
Descr: Text

Then the report could text box could use the expression:

=DLookup("Descr","Classifications","GP='" & CatGP & "' AND
POP='" & CatPOP & "'")

This would allow you to change the descriptive word without
having to find and edit you program when someone objected to
being designated as a plowhorse ;-)
 
Dear Mr. Marshall Barton SIR!!!
You are a genious ::)) I have tried your first suggestion:-
=IIf([CatGP]="High",IIf([CatPOP]="High","Star","Puzzle"),
IIf([CatPOP]="High","Plowhorse","Dog"))
and it works like a charm. THANK YOU!!!!!
I should add that the 'Classification' is not for people, but rather it is
used for
classifying the Popularity and Profitability of Restaurant Menu Items.
Once again thank you and have good day - all the best for 2005.
Mike

Marshall Barton said:
hotcat said:
I have built a Report in Access 2003 that calculates quite a lot of fields,
two of which calculate and return text values "High" or "Low" as their answer.
Then there is another field "Classification" that, in turn, uses the above
two (calculated) fields -"CatGP" and "CatPOP" for its own calculation ... In
Excel the expression is as follows:-

=IF(AND(N11="High",O11="High"),"Star",IF(AND(N11="Low",O11="High"),"Plowhorse",IF(AND(N11="High",O11="Low"),"Puzzle",IF(AND(N11="Low",O11="Low"),"Dog",""))))

Naturally in the Access Report the "Cell references" would be replaced with
the two fields -"CatGP" and "CatPOP".

I have tried the above Excel formula and replaced "IF" with "IIF" and the
Cell references with the two fields as above. But I cannot get it to work.

If I use ONLY the following 'formula'
=IIf([CatGP]="High",IIf([CatPOP]="High","Star"),"")
it does actually evaluate to "Star" where both "CatGP" and "CatPOP" have a
value of "High".

Could you PLEASE help by showing me what I should be doing for the field
"Classification" to evaluate the following,

=IIf ([CatGP]="High",IIf([CatPOP]="High","Star"),
=IIf ([CatGP]="Low",IIf([CatPOP]="High","Plowhorse"),
=IIf ([CatGP]="High",IIf([CatPOP]="Low","Puzzle"),
=IIf ([CatGP]="Low",IIf([CatPOP]="Low","Dog"),""))))
Click to expand...


Actually, that looks like it might work if you just tool out
all but the first = sign.

OTOH, it could be shortened somewhat to:

=IIf([CatGP]="High",IIf([CatPOP]="High","Star","Puzzle"),
IIf([CatPOP]="High","Plowhorse","Dog"))

OTOOH, you would probably be better off over the long haul
if you put all that info into a "lookup" table and retrieved
the classification from there.

Table Classifications:
GP: Text
POP: Text
Descr: Text

Then the report could text box could use the expression:

=DLookup("Descr","Classifications","GP='" & CatGP & "' AND
POP='" & CatPOP & "'")

This would allow you to change the descriptive word without
having to find and edit you program when someone objected to
being designated as a plowhorse ;-)
Click to expand...
 
Back
Top