IIF function question

[smithjp4]

Hello everyone,
I’ve created an accounts payable database where we can track invoices by the amount of days each invoice is aging, the amount of the invoice and the organization that we’re paying the invoice for. I’m now trying to create a query that will give a dashboard snapshot showing the organization, the total number of invoices that are 21 days or older and the sum total of those invoices…got that part.
Here’s where I’m hitting a snag:
In the next three columns, I want to break out the total number of invoices to reflect how many are:
>=21 and <46 (column will be titled “Yellow”)
>=45 and <60 (column will be titled “Orange”)
>=60 (column will be titled “Red”)

Here’s what I’ve tried:
Yellow: Count(IIf([Days Aging]>=21 and <46))
Yellow: Count(IIf([Days Aging]>=21 and <46, 1, 0))
Yellow: Sum(IIf([Days Aging]>= 21 and <46))

The query I’m pulling the information from contains a Days Aging column, and the data in that column is a numeric value.

Any suggestions?

 

[jzwp22]

Welcome to the forum!

I am thinking that you will need to use either a series of nested queries or series of domain aggregate functions (DCount() and DSum). In order to help you further, however, it would be nice to see the SQL text of your current query as well as the query on which that query is built (i.e. the one with the Days Aging field).

 

[Brianwarnock]

You don't say what happened
Try
Yellow: Sum(IIf([Days Aging]>= 21 and <46,1,0))

Brian

 

[Taruz]

hi..

as though this more accurate..

Sum(IIf([Days Aging]>=21 and [Days Aging]<46,1,0))

 

[Brianwarnock]

You are correct sir, I am getting careless in my old age.

Brian

 

[smithjp4]

Sum(IIf([Days Aging]>=21 and [Days Aging]<46,1,0))

That worked!

Thank you all SO much!