Hello,

In a query I have 3 seperate columns outputting information based on 3 seperate built expressions. They each are outputting the invoice amount by the date they were due. One is doing invoice amounts from 31-90 days over due, one is doing 91-180 days over due, and the other is doing over 180 days over due.

The code looks as such:


31-90 days over:

Highest 31-90: Sum(IIf([NET_DUE_DATE] Between Date()-31 And Date()-90,([G/L Principl Amt])*0.25,0))


91-180 days over:

Highest 91-180: Sum(IIf([NET_DUE_DATE] Between Date()-91 And Date()-180,([G/L Principl Amt])*0.5,0))


Over 180 days over:

Highest Over 180: Sum(IIf([NET_DUE_DATE]<=Date()-180,([G/L Principl Amt])*1,0))



Is there any way to put all three of those statements in one column so that I can sort to see which invoices are the greatest at their specified date over due?

Thank you

Hi..

Assuming all the sum works ok, you can do a really complicate iif... something like

iif(sum1>sum2,iif(sum1>sum3,sum1,iif(sum3>sum2,sum 3,sum2)),iif(sum3>sum2,sum3,sum2))

now go and try to translate it :)

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

Ok. Thank you for the reply but, I don't think that is what I am looking for.

I want to be able to display all the values, like I am with the original three seperate columns, just in one column and then just to be able to click the sort desceding button to give me the highest values.

For example, I just want to be able to put all three of the strings together like this, I just don't know the correct syntax to do this:

Highest: Sum(IIf([NET_DUE_DATE] Between Date()-31 And Date()-90,([G/L Principl Amt])*0.25,0)), Sum(IIf([NET_DUE_DATE] Between Date()-91 And Date()-180,([G/L Principl Amt])*0.5,0)), Sum(IIf([NET_DUE_DATE]<=Date()-180,([G/L Principl Amt])*1,0))

Ok, sorry but then I don't understand what you need. The iif returns the max value. But you want to see the three values in one cell?? and then make access sort them by one of them?

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

I thought of the same thing as Gonzalo. That is, create a fourth column that compares sum1 to sum2 to sum 3 and then give the highest of the three sums. You can then sort by the fourth column which will give you the max of three sums sorted like you said.

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division