Using the IIf Function?

[djclntn]

Can the IIf Function be used in referencing then more than 1-fld? What do I mean? In my MLB dbs I have the following flds:


- HmRd: Either "H" for Home games or "R" for Road Games.
- Team
- Score
- AResults: "W" = win, "L" = Lost
- W: IIf([AResults]="W",1,0) - that works
- L: IIf([AResults]="L",1,0) - that works
- HW: IIf([AResults]="W" & [HmRd]="H",1,0) that DOESN'T work
- RW: IIf([AResults]="W" & [HmRd]="R",1,0) that DOESN'T work

Does anyone follow my question. Is the IIf Function exculsively just involves 1-fld in it's statement or can multiply flds be in an IIf statement?

Thanks

[pbaldy]

You can test multiple fields. You would separate each with And or Or rather than &, which is a on concatenation operator.

[djclntn]

Thanks pb Ok, I got the "And" concept, but not the "Or". In my NHL dbs the 'W' fld gets a 1 if the AResults fld either has a "W" or a "W\SO"

I tried:
- W: Iif([AResults]="W",1,0" Or "W\SO",1,0)
- W: Iif([AResults]="W",1,0" Or Iif[AResults]="W\SO",1,0)

[pbaldy]

No, like

HW: IIf([AResults]="W" And [HmRd]="H",1,0)

In that situation you use And or Or as appropriate to the logic you need (both criteria must be met or either one).

[djclntn]

Why isn't this expression working? I want a 1 in the 'W' field/column if eithr in thhe AResults field has either a "W" or a "W\SO"

W: IIf([AResults]="W", Or [AResluts]="W\SO",1,0)

[pbaldy]

You added a comma in the middle of the test.

[djclntn]

W: IIf([AResults]="W" Or IIF[AResluts]="SOW",1,0) Still doesn't work

[djclntn]

Why when I have, W: IIf([AResults]="W" Or IIF[AResluts]="SOW",1,0) I also have a "1" in the OTL fld/column?
AND
When I have, OTL: IIf([AResults]="OTL" Or "SOL",1,0) I'm also getting a "1" in the fld/column?

BTW: just for clairity, I changed "W\SO" to "SOW"

NHL 101
You can have 3-types of results per game
- W: Win
- L: Lost
- OTL: Overtime Lost

I chose to add 2-more Results:
- SOW: Shoot Out Win which I can write an IIf statement in my SOW fld/column - SOW: IIf([AResults]="SOW",1,0)
- SOL: Shoot Out Lostwhich I can write an IIf statement in my SOL fld/column - SOL: IIf([AResults]="SOL",1,0)

My problem I'm encounting is, in the "W" (Win) fld/column I'm trying to set up an IIf statement whereby a "1" is entered in the "W" fld/column if the AResults fld/column either as a, "W" or a "SOW"

Likewise with the "OTL" fld/column, in the "OTL" (Overtime Lost) fld/column I'm trying to set up an IIf statement whereby a "1" is entered in the "OTL" fld/column if the AResults fld/column either as a, "OTL" or a "SOL"

I don't know if/how an IIf statement can be written with more then one value in a fld?

[pbaldy]

You're not repeating the field:

OTL: IIf([AResults]="OTL" Or "SOL",1,0)

would be

OTL: IIf([AResults]="OTL" Or [AResults]= "SOL",1,0)

[djclntn]

Thanks Paul...

[djclntn]

Paul,
Somthing still isn't right. with these 2-expressions I'm getting a "1" in both the 'HW' Fld & the 'AW' fld when in the 'AResuults' field is "W"...?

HW: IIf([AResults]="W" Or [AResults]="SOW" And [HWAW]="H",1,0)
AW: IIf([AResults]="W" Or [AResults]="SOW" And [HWAW]="A",1,0)

[pbaldy]

When you mix And & Or, you should add parentheses to clarify the desired logic:

HW: IIf(([AResults]="W" Or [AResults]="SOW") And [HWAW]="H",1,0)
HW: IIf([AResults]="W" Or ([AResults]="SOW" And [HWAW]="H"),1,0)

More simply:

(A And B) Or C
A And (B Or C)

See how the logic can change? You don't want Access applying it's own.

[djclntn]

Paul,
Somthing still isn't right. with these 2-expressions I'm getting a "1" in both the 'HW' Fld & the 'AW' fld when in the 'AResuults' field is "W"...?

HW: IIf([AResults]="W" Or [AResults]="SOW" And [HWAW]="H",1,0)
AW: IIf([AResults]="W" Or [AResults]="SOW" And [HWAW]="A",1,0)

[djclntn]

Sorry, I DID NOT men to repost!

[pbaldy]

Does that mean it's working now?