Or Expression

LadyDiss

Registered User.
Local time
Today, 09:20
Joined
Aug 29, 2001
Messages
31
HELP!! :-) Working on a deadline and this query won't work. Hopefully someone can see something that is obviously. Different criteria apply to different levels . . . any ideas of different OR operator to make this work or do you see something else obvious?

Levels: (IIf([SumofMth Life Pols]>=20,IIf([Bank/Mutual Funds]>=4,IIf([SumOfMth Health LTC]>=2 Or [Fortis/MedSupp]>=4,"Level 3")))) Or (IIf([SumOfMth Life Pols]>=15,IIf([Bank/Mutual Funds]>=3,IIf([SumofMth Health LTC]>=1 Or [Fortis/MedSupp]>=3,"Level 2")))) Or (IIf([SumOfMth Life Pols]>=10,IIf([Bank/Mutual Funds]>=2,IIf([SumofMth Health LTC]>=1 Or [Fortis/MedSupp]>=2,"Level 1"))))
 
I beg your pardon and I may be misreading this, but it looks like you are testing over and over again if a sets of numbers are greater than given values.

Take your first If statement. Life >= 20, Bank >= 4, LTC >= 2 and Fortis >=4.

Now if you are testing Data where Life = 25, Bank = 5, LTC =3, and Fortis = 5, you would want a level three yes? But the second statement asks if it meets this additional criteria in your OR statement. Life >= 15... well of course it is, the number was 25... it is greater than 20 AND it is greater than 15. As well as Bank = 5 is still greater than 3 for your level 2 and so on down the line.

You need to work with a range of numbers for your lower levels. Not just the greater than symbol.


Or (IIf([SumOfMth Life Pols]>=15 AND [SumOfMth Life Pols] <= 19 ,IIf([Bank/Mutual Funds]>=3 IIf([SumofMth Health LTC]>=1 Or [Fortis/MedSupp]>=3,"Level 2"))))

Does this help? That's what I see.... but others may have additional insight.
Tess
 
You have a syntax error here.

To arrive at Level 3, you have nested two IIf() in an IIf():-
Levels: (IIf([SumofMth Life Pols]>=20,IIf([Bank/Mutual Funds]>=4,IIf([SumOfMth Health LTC]>=2 Or [Fortis/MedSupp]>=4,"Level 3"))))

However, the syntax of an IIf() consists of three parts:-
IIf(expr, truepart, falsepart)

So if you nest two IIf() in an IIf(), you will have:-
IIf(expr, IIf(expr, IIf(expr, truepart, falsepart), falsepart), falsepart)

Substituting with your code, it should have been:-
IIf([SumofMth Life Pols]>=20, IIf([Bank/Mutual Funds]>=4, IIf([SumOfMth Health LTC]>=2 Or [Fortis/MedSupp]>=4, "Level 3", falsepart), falsepart), falsepart)

Hence you have left out all three falseparts.
-----------------------------------

You should have used AND instead of two nested IIf() in each level:-

Levels:
IIf([SumofMth Life Pols]>=20 AND [Bank/Mutual Funds]>=4 AND ([SumOfMth Health LTC]>=2 Or [Fortis/MedSupp]>=4),"Level 3",
IIf([SumOfMth Life Pols]>=15 AND [Bank/Mutual Funds]>=3 AND ([SumofMth Health LTC]>=1 Or [Fortis/MedSupp]>=3),"Level 2",
IIf([SumOfMth Life Pols]>=10 AND [Bank/Mutual Funds]>=2 AND ([SumofMth Health LTC]>=1 Or [Fortis/MedSupp]>=2),"Level 1","")))


But your criteria for the levels are overlapping. Any record that satisfies Level 3 also satisfies both Level 2 and Level 1 (because you have used >= in your code), though Access will simply return the first level it satisfies, i.e. Level 3. Unless this is what you intended, you should check your criteria for each level.

If what you want are ranges, you should use BETWEEN...AND instead of >=, e.g.
[SumOfMth Life Pols] BETWEEN 15 AND 19
 
You're exactly right in your observations! We finally got this to work, thanks for all your input!
 
On this same query, now we want to only return the "Level" personnel. Is it possible to query an expression? I can't get it to work. I have tried in the criteria to put "is not null", it returns no results or "Level 1" etc., but it won't run on the expression. Ideas?
 
Sorry, Lady..
What exactly are you trying to do and what have you tried so far?
 
Finally got this to work, we queried the query and then put the criteria in and it worked. thanks.
 

Users who are viewing this thread

Back
Top Bottom