Can I use the 'IF' word in queries?

[Malcolm Potter]

I have a query based upon two tables which contains, among others, two
number fields (LastYearsMatches and ThisYearsMatches). I would like to add a
calculated field named 'Qualified?' to this query such that this field
contains one of three text strings, dependant upon the values in the two
number fields as follows:



If both of the fields contain numbers which are >= 17, then the calculated
field should show "Yes".

If the LastYearsMatches field contains a number >= 17 and the
ThisYearsMatches field contains a number < 17, then the calculated field
should show "Not Yet".

If the LastYearsMatches field contains a number < 17, then the calculated
field should show "Can Not".



Do I need something like a three level 'If --- Else If' arrangement within a
For Next Loop of some description and if not, what?



All suggestions gratefully received. I am a comparative newbie to Access and
am running Access 2002.



Regards and the compliments of the season.



Malcolm

 

[Douglas J. Steele]

There's IIf (Immediate If) that you can use in queries (and you can nest
them). The basic syntax is:

IIf(<condition to check>, <action if condition is true>, <action if
condition is false>)

Qualified: IIf([LastYearsMatches] >= 17 And [ThisYearsMatches] >= 17, "Yes",
IIf([LastYearsMatches] >= 17 And [ThisYearsMatches] < 17, "Not Yet", "Can
Not"))

Note that that needs to all be on one line.

 

[Glenn]

You could try the following:

Qualified: iif(nz([LastYearsMatches],0) < 17, "Can Not",
iif(nz([ThisYearsMatches],0)>16, "Yes", "Not Yet"))

Regards,
Glenn

 

[Les]

Hi Malcolm,
Once you have the 2 tables in your query, create a new
expression in the next blank grid. Type the following:
Qualified:IIF(LastYearsMatches<17,"Can Not",IIF
(ThisYearsMatches<17,"Not Yet","Yes"))

This should accomplish what you need.

 

[Malcolm Potter]

Thanks guys. Your suggestions have given me the required result. Now I can
move on to the next problem.

Regards

Malcolm