If statment as condition in query

[bjschon]

I want to build a query based upon user chosen fields. The following
condition will pull all accounts with "credit scores" greater than or equal
to the "tier condition". The tier1Operator and Tier1Condition is maintained
by the user and stored in another table. So, for example, if the user chose
a credit score greater than or equal to 720 as Tier1Condition, I want this
query to pull all accounts with credit scores of 720 or higher. The
following criteria isn't pulling all of them, only a few accounts. Not sure
if my statement is incorrect..help please!

IIf([Tier1Operator]="Greater than or equal to",[Credit
Score]>=[Tier1Condition])

Thanks, Brad

 

[Clifford Bass]

Hi Brad,

A couple of things to do--see if they make a difference. The IIf()
function takes three parameters and you have only specified two so you might
want to try:

IIf([Tier1Operator]="Greater than or equal to",[Credit
Score]>=[Tier1Condition], False)

I am guessing that you are comparing the results of this IIf() to True.
Is that accurate?

The other thing is that I have had poor results for some reason when
using a comparision in the second or third parameters of the IIf(). So you
may want to try:

IIf([Tier1Operator]="Greater than or equal to" and [Credit
Score]>=[Tier1Condition], True, False)

Or:

IIf([Tier1Operator]="Greater than or equal to", If([Credit
Score]>=[Tier1Condition], True, False), False)

Hope that helps,

Clifford Bass

 

[bjschon]

Hi Clifford. Thanks for the quick response. I'm actually comparing the
results of this IIF() to a number, or the credit score. So, in this example,
I want the query to pull all accounts with credit scores above or equal to
the [TierCondition], which is the credit score parameter set by the user (720
in this case).

I tried your suggestions, but it still doesn't work. If I put [Credit
Score]>=[Tier1Condition], it works fine. But when I input that expression in
the IIF statement, the query is wrong.

Hi Brad,

A couple of things to do--see if they make a difference. The IIf()
function takes three parameters and you have only specified two so you might
want to try:

IIf([Tier1Operator]="Greater than or equal to",[Credit
Score]>=[Tier1Condition], False)

I am guessing that you are comparing the results of this IIf() to True.
Is that accurate?

The other thing is that I have had poor results for some reason when
using a comparision in the second or third parameters of the IIf(). So you
may want to try:

IIf([Tier1Operator]="Greater than or equal to" and [Credit
Score]>=[Tier1Condition], True, False)

Or:

IIf([Tier1Operator]="Greater than or equal to", If([Credit
Score]>=[Tier1Condition], True, False), False)

Hope that helps,

Clifford Bass

I want to build a query based upon user chosen fields. The following
condition will pull all accounts with "credit scores" greater than or equal
to the "tier condition". The tier1Operator and Tier1Condition is maintained
by the user and stored in another table. So, for example, if the user chose
a credit score greater than or equal to 720 as Tier1Condition, I want this
query to pull all accounts with credit scores of 720 or higher. The
following criteria isn't pulling all of them, only a few accounts. Not sure
if my statement is incorrect..help please!

IIf([Tier1Operator]="Greater than or equal to",[Credit
Score]>=[Tier1Condition])

Thanks, Brad

 

[Clifford Bass]

Hi,

If you want to compare the results of the IIf() function to a number,
then you have to tell it to return a number. But if you return a credit
score, how then do you know to use the >= outside of the IIf() statement? It
may be better to just eliminate the IIf() function altogether.

select .....
from ....
where ([Tier1Operator] = "Greater than or equal to" and [Credit Score] >=
[Tier1Condition]) or ([Tier1Operator] = "Less than or equal to" and [Credit
Score] <= [Tier1Condition]) or ([Tier1Operator] = "Equal to" and [Credit
Score] = [Tier1Condition]) or ..... or .....

Clifford Bass

 

[bjschon]

Good point. Thanks, Clifford. I appreciate the help! Enjoy your day!

Brad

Hi,

If you want to compare the results of the IIf() function to a number,
then you have to tell it to return a number. But if you return a credit
score, how then do you know to use the >= outside of the IIf() statement? It
may be better to just eliminate the IIf() function altogether.

select .....
from ....
where ([Tier1Operator] = "Greater than or equal to" and [Credit Score] >=
[Tier1Condition]) or ([Tier1Operator] = "Less than or equal to" and [Credit
Score] <= [Tier1Condition]) or ([Tier1Operator] = "Equal to" and [Credit
Score] = [Tier1Condition]) or ..... or .....

Clifford Bass

Hi Clifford. Thanks for the quick response. I'm actually comparing the
results of this IIF() to a number, or the credit score. So, in this example,
I want the query to pull all accounts with credit scores above or equal to
the [TierCondition], which is the credit score parameter set by the user (720
in this case).

 

[Clifford Bass]

Hi Brad,

You are welcome. And enjoy you day too!

Clifford Bass