multiple criteria in IIF condition

[jerry28ph]

Hi All,

How can I write a query using IIF condition with multiple condition, I have 3 course_code [CS, BSN, HRM] and i need to display the corresponding description in the form not in report. I created a form, having 2 text boxes for course code and for the description, and command button that once you click it, the SQL will run and it will display the description on textbox.
Please need you help.

Thank so much.

Regards,
Jerry

 

[flect]

You should be able to string it together with OR

=IIF([name] ="John" OR "Fred" OR "Tom", "Manager", "Staff")

 

[jerry28ph]

Hi Flect,

Please bear with me, Im new in Access so I really eager to learn more. Anyways, thank you for the reply.

You should be able to string it together with OR

=IIF([name] ="John" OR "Fred" OR "Tom", "Manager", "Staff")

 

[jerry28ph]

Hi flect,

what's wrong on this query, I have 6 records and 3 of them has cc_code equals to a,b,c and the rest are e,f,g. I believe cc_code = abc description would be "BS" otherwise "VOCATIONAL". When I ran the query all DESCRIPTION are "BS".

SELECT tab_course.cc_code, tab_course.cc_desc, IIf([cc_code]="a" Or "b" Or "c","BS","VOCATIONAL") AS Description
FROM tab_course;

Is syntax correct?

Thanks

Hi Flect,

Please bear with me, Im new in Access so I really eager to learn more. Anyways, thank you for the reply.

 

[flect]

I'm still fairly new to this too,

It looks like you need to split up your command.

As long as your data is pulled up in the form properties; it's just a matter of programming the appropriate text box


You need to have the SELECT command as the Record Source in properties/data for the form (unless you're doing it in VB, in which case i'm not to sure what to do)
eg: form/data - record source:

 SELECT tab_course.cc_code, tab_course.cc_desc AS Description
FROM tab_course;

the IIF should go in the Control Source in the properties section of the text box you want to display.
eg: Text Box/data - Control Source:

=IIf([cc_code]="a" Or "b" Or "c","BS","VOCATIONAL")

 

[Mike375]

Flect,

When using IFF and Or the field name needs to be repeated and best for text to use Like

So instead of this

=IIF([name] ="John" OR "Fred" OR "Tom", "Manager", "Staff")

try this

IIF([Name] Like "John" Or [Name] Like "Fred" Or [Name] Like "Tom","Manager","Staff")

 

[raskew]

Try:

Iif(instr("abc", [cc_code])>0, "BS", "VOCATIONAL") as Description

...as a workaround.

Bob

 

[flect]

Flect,

When using IFF and Or the field name needs to be repeated and best for text to use Like

So instead of this

=IIF([name] ="John" OR "Fred" OR "Tom", "Manager", "Staff")

try this

IIF([Name] Like "John" Or [Name] Like "Fred" Or [Name] Like "Tom","Manager","Staff")

Thanks Mike,

I haven't actually had to use OR in any of my IIF's - i just had a quick look at the MS site to find you in fact can use AND/OR in IIF syntax

 

[Mike375]

Thanks Mike,

I haven't actually had to use OR in any of my IIF's - i just had a quick look at the MS site to find you in fact can use AND/OR in IIF syntax

But you get the wrong answer. Here is three examples.

PaymentMode has entries of Y, H, Q, M and Mth

IIf([PaymentMode] Like "M" Or [PaymentMode] Like "Mth",[RegPremium]*12,Null) That gives corrent answer. That is where PaymentMode is M or Mth the RegPremium is X 12 and for Y, H and Q answer is Null

IIf([PaymentMode] Like "M" Or "Mth",[RegPremium]*12,Null) That multiples RegPremium X 12 for every record

Exp3: IIf([PaymentMode]="M" Or "Mth",[RegPremium]*12,Null) That also multiples RegPremium X 12 for every record.

This Access 2003 and that has been the case back to at least A95. Don't know about A2007