If statment as condition in query

B

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
 
C

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
 
B

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.

Clifford Bass said:
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 said:
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
 
C

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
 
B

bjschon

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

Brad

Clifford Bass said:
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 said:
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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top