IIf statement help

[joolsUK0575]

Hi there

I am having problems with an IIF statement and I was wondering how I can get the query that I need to work.

I have the following columns

application_type: this has three values in it - Major, Minor and Other
numberofdaystodecide: this is the number of days taken to decide a file

what i want to do is an iif statement that works as follows

iif the application type = major and the numberofdaystodecide >92 then over 13 weeks

iif the application_type = major and the numberofdaystodecide <=92 then less than 13 weeks

iif the application_type = minor and the numberofdaystodecide >56 then over 8 weeks

iif the application_type = minor and the numberofdaystodecide <=56 then less than 8 weeks

iif the application_type = other and the numberofdaystodecide >56 then over 8 weeks

iif the application_type = other and the numberofdaystodecide <=56 then less than 8 weeks

Any ideas how I put this in to a query to get the information that I need?

Tried a couple of things to no avail

Thanks for any help

Jools

 

[KenHigg]

So you're saying that depending on the criteria, you either want the text string: "over 13 weeks" or "less than 13 weeks" to show up in a column of the query you create on the fly named 'application type'?

 

[joolsUK0575]

Hi

What I want is for the new data to appear in a new column

I know that the query will start out as

PeriodOfTime: Iif ([application_type]...........

But from here on in I get stuck because of the parameters

 

[JoeComputer]

Try this...

This code will work IF:

"Major", "Minor", and "Other"

...are the only possible choices for application type, and:

>92 or <=92
and
>56 or <=56

...are the only checks for numberofdaystodecide. If you're going to add more later, then this code will have to be expanded.

iif(application type = major, iif(numberofdaystodecide>92, "Over 13 Weeks", "Less than 13 weeks"), iif(numberofdaystodecide >56, "Over 8 Weeks", "Less than 8 Weeks") )

This is what it does:

Checks to see if it's Major, then checks to see if it's >92. If it's not Major, checks to see if it's >56 (since Minor and Other have the same critieria).

Hope this helps.

 

[Keith Nichols]

Would select case do it?

Forgive the many errors that are bound to be in the code below - I'm new. I don't know if a select case has any advantage over the IIF but it may work just as well.

Select Case application_type
Case "Major" And numberofdaystodecide >92
weeks_field = "more than 13 weeks"

Case "Major" And numberofdaystodecide <=92
weeks_field = "less than 13 weeks"

Case "Minor" And numberofdaystodecide >56
weeks_field = "more than 8 weeks"

Case "Minor" And numberofdaystodecide <=56
weeks_field = "less than 8 weeks"

Case "Other" And numberofdaystodecide >56
weeks_field = "more than 8 weeks"

Case "Other" And numberofdaystodecide <=56
weeks_field = "less than 8 weeks"

End Select

Regards,

Keith

 

[raskew]

Hi -

Given your example, think you can pull this off with a couple of Iif() statements.
The application type may be identified by pulling just the second letter (as a unique identifier) in the type, i.e. "a" = Major, "i" = Minor, "t" = Other.

Minor and Other both use the same criteria (56 days) so you use the same logic on both.

Try this query, substituting your table/field names.

SELECT
    tbl1.ApType
  , tbl1.NumDays
  , IIf(InStr("ait",Mid([ApType],2,1))=1,92,56) AS Expr1
  , IIf([expr1]>=[NumDays],"Less Than ","Over ") & [expr1] & " days." AS Expr2
FROM
   tbl1;

Have to ask: 7 * 8 = 56 (eight full weeks); 7 * 13 = 91 (thirteen full weeks). Why the disparity of 13 weeks + 1 day (92)

HTH - Bob