Multiple IIF statements in one column?

[bwalk037]

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))



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))


What would be the correct syntax to do this?



Thank you

 

[ericwfrost]

What would be the correct syntax to do this?

I think you just use the Ampersand --

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))

I put a space in there, you might want some other separator.

hope this helps!

Eric

 

[bwalk037]

Hello. Thank you for responding.

This worked to be able to display all the information but, when I sort it groups the sort by the different strings not as just one string.

Example right now it is sorting it like this:

10
9
8
11
10
8
12
11
10

And I need it to sort like this:

12
11
11
10
10
10
9
8
8

Do you know how I would be able to make access see it as one string?

 

[ericwfrost]

Maybe I am dense, but I am having trouble picturing what you are asking...

before you had three columns

Highest 31-90 | 91-180 days over | Over 180 days over
1 | 1 | 1
2 | 2 | 2

Now you have one column

Highest
1 1 1
2 2 2

Now when you sort, it's going to sort only on the first number..

What do you want?

Eric

 

[bwalk037]

Right now I have the one column. But I would like the one column to be able to sort the 3 columns as one.

So instead of

1 1 1
2 2 2

I need all three of those in one line

1
1
1
2
2
2

Does that make sense?

I am trying to see what the top 10 numbers are from all three columns and if I can get all three columns in one line I can just sort it and copy the top 10 numbers.

 

[ericwfrost]

Ah yes - then you should probably use a Union query...

Do three separate queries, each with only one column.

Then do a fourth Union query to combine the three..

In the design view of the fourth query, do to Query -> SQL Specific -> Union.

The code would look something like this.

select * from qry31_90
union
select * from qry91_180
union
select * from qryOver180;

Search for Union if you need more help, but I think that should put you on the right track. The operation you want to perform is a Union.

hope this helps.

Eric

 

[bwalk037]

OMG, you are a genius!!! That worked perfectly!!! Now, I just have to do a little fine tunning and I will be done!!

Thank you again!!! You truely are a life saver !!!

 

[ericwfrost]

Hehe, I think I went years using Access before someone showed me union. There's still a lot I don't know. But Union is one of the useful things I've used quite a bit after learning how to do it.

Eric

 

[boblarson]

But Union is one of the useful things I've used quite a bit after learning how to do it.

That is true - and as long as you remember the ONE rule of Union queries - Each query that makes up the Union must have the same number of fields. They do not even have to be the same datatype (although if you want to do things with them you should make sure to match datatypes).

 

[bwalk037]

haha. This makes me wonder what else Access can do that I had no idea about. I have been using Access professionally for about 3 months now but, I used it all through out college and I had no idea about the "union" function.

Thank you again, now to figure out what else I am missing out on in Access!