Conditional Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Have tried to find the answer to this question here but have not.

I am building a query to identify the correct PropertyCode for each record
based on the dollar amount of the record. There are two options. If the
dollar amount is above the threshold the record would have a code assigned
from the PropertyCode field. If the dollar amount is below the threshold,
the record would have a code assigned from the AggregatePropertyCode field.
My expression is:

IIf([Amount]>=[AggregateReportingAmt],[PropertyCode],[AggregatePropertyCode])

I expected the query to show all records requested. Some would have a code
from PropertyCode and the rest would have a code from AggregatePropertyCode.
However, I am only getting the records wtih a code from PropertyCode. Am I
using the correct expression? If not can you suggest the correct expression.

Thanks for your help.
 
Dear Kerry:

I believe you are saying that [Amount] is not always >=
[AggregateReportingAmt]. I recommend you find out quickly and easily which
rows, if any, have this condition.

Temporarily add a filter:

WHERE [Amount] < [AggregateReportingAmt]

You should then see the rows which should be showing the
AggregatePropertyCode.

Tom Ellison
 
Hi Tom,
I don't believe I was clear in my first posting. You are correct in that
for some records the amount is less than the [AggregateReportingAmt] while
others are greater. I am querying on a field called State and would expect
to get both types of records in the results. The problem I am encountering
is that I had hoped by using an IIF statement I could add a field from
another table and populate it with either the AggregatePropertyCode or the
PropertyCode. As I write this I see that my problem is that each type of
code is in a separate field. I will have to reevaluate the table before
proceeding. Thanks for your help.
--
KerryJ


Tom Ellison said:
Dear Kerry:

I believe you are saying that [Amount] is not always >=
[AggregateReportingAmt]. I recommend you find out quickly and easily which
rows, if any, have this condition.

Temporarily add a filter:

WHERE [Amount] < [AggregateReportingAmt]

You should then see the rows which should be showing the
AggregatePropertyCode.

Tom Ellison


KerryJ said:
Hi,
Have tried to find the answer to this question here but have not.

I am building a query to identify the correct PropertyCode for each record
based on the dollar amount of the record. There are two options. If the
dollar amount is above the threshold the record would have a code assigned
from the PropertyCode field. If the dollar amount is below the threshold,
the record would have a code assigned from the AggregatePropertyCode
field.
My expression is:

IIf([Amount]>=[AggregateReportingAmt],[PropertyCode],[AggregatePropertyCode])

I expected the query to show all records requested. Some would have a
code
from PropertyCode and the rest would have a code from
AggregatePropertyCode.
However, I am only getting the records wtih a code from PropertyCode. Am
I
using the correct expression? If not can you suggest the correct
expression.

Thanks for your help.
Click to expand...
Click to expand...
 
Back
Top