Calculated field with multiple criteria

[Fipp]

I have a table that contains information about plays in a football game. In
the table there are the following 3 fields:

1. Down
2. Distance
3. Gain

I would like to make another field calculated off of those 3 fields Based of
the following criteria.

If down is = 1 and gain is < 4 I would like the result of the calculated
field to be "Yes"
If down is = 2 and gain is < 1/2 the value of distance the calculated field
should = "Yes"
If down is =3 and gain is < distance the value of the calculated fied should
= "Yes"
If down is =4 and gain is < distance the value of the calculated fied should
= "Yes"

Any recomendations on how to accomplish this would be much appreciated.

 

[KARL DEWEY]

Try this --
SELECT fipp.down, fipp.distance, fipp.gain, IIf(([down]=1 And [gain]<4) Or
([down]=2 And [gain]<([distance]/2)) Or (([down]=3 Or [down]=4) And
[gain]<[distance]),"Yes","No") AS x
FROM fipp;

 

[Fipp]

Is there a way to make one if down = 1 and gain = null result should be "yes"
I tried to add the following in but it doesn't seem to be changing it?

(([dn]=1 And [gain]=Null)

 

[KARL DEWEY]

It should not be Null but maybe 0 ( zero ).