Can I use the 'IF' word in queries?

  • Thread starter Thread starter Malcolm Potter
  • Start date Start date
M

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
 
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.
 
You could try the following:

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

Regards,
Glenn
 
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.
 
Thanks guys. Your suggestions have given me the required result. Now I can
move on to the next problem.

Regards

Malcolm
 
Back
Top