IIf statement troubles

[joolsUK0575]

Hi there

I'm new to the forum and was hoping a kind person out there could help me out with a problem.

Basically I work for a local authority and each week we produce a list of planning applications for our Parish Council's.

Part of the query includes the category used by the ODPM (Office Of Deputy Prime Minister). This category is basically a number. 1 - 5 being Major, 6 - 10 being Minor and 11 - 17 being Other.

I am trying to write an expression which will say that when it is category 1 it is major, 6 minor 13 other etc.

The following expression seemed to work fine on 1 - 5:

Category: IIf([DOE_Cat] like "1", "Major", [DOE_Cat])

It's when I go to to add in a second IIf statement that the trouble starts. I found a post that I hoped would get over my problem but it only added in the number to the new column. I tried the following:

Category: IIf(DOE_Cat] like "1", "Major", [DOE_Cat]) & IIf([DOE_Cat] like "6", "Minor", [DOE_Cat])

Does anyone have any ideas to get it working correctly as it's driving me crackers!!

Thanks

Jools

 

[Rich]

Use a SelectCase function

 

[namliam]

Use a second table containing the categories & naming of them
Then link it in the query....

--FYI--
Use like for wildcards...

the "proper" way to nest Iifs:
Category: IIf(DOE_Cat] like "1", "Major", IIf([DOE_Cat] like "6", "Minor", [DOE_Cat]))

But go with the extra table, believe me.... you will love it....

Regards

 

[joolsUK0575]

Hi Mailman

Thanks for the answer. I'll see how I go when I get back to work in the morning.

I know that the easiest way is add in an extra column BUT it is not as simple as that. The database is based in SQL Server and we can not change the structure of the tables as it would mess up our maintenance contract with our software supplier.

Looking forward to trying out what you sent

Thanks for your input

Jools

 

[raskew]

The Choose() function is highly underrated, but it can work very well
provided you can massage your options into a 1-2-3 configuration.

Given your initial description, here's how you could return "Major", "Minor" or
"Other" based on the value of DOE_Cat. Try this in the debug window. To
use it with fields in a query, enclose DOE_Cat in brackets, i.e. [DOE_Cat]:

DOE_Cat = 12
? choose(int(DOE_Cat / 5) + iif(Doe_Cat mod 5 > 0, 1, 0), "Major", "Minor", "Other")
Other

DOE_Cat = 2
? choose(int(DOE_Cat / 5) + iif(Doe_Cat mod 5 > 0, 1, 0), "Major", "Minor", "Other")
Major

DOE_Cat = 5
? choose(int(DOE_Cat / 5) + iif(Doe_Cat mod 5 > 0, 1, 0), "Major", "Minor", "Other")
Major

DOE_Cat = 7
? choose(int(DOE_Cat / 5) + iif(Doe_Cat mod 5 > 0, 1, 0), "Major", "Minor", "Other")
Minor

HTH - Bob