Ugly Iif Statement

[Kathleen Badeau]

I would love to have some friendly advice. I am trying to help a coworker and she is having a problem with the Iif statement she is using as Access says it is now too long.

She has a database that records donations made by trustees for two companies. Company A trustees have a budget for Matching and a budget for Nonmatching donations each year. She needs to pull a report, by Trustee that shows what was spent against his Matching Donation (by Calendar Year) and then a separate report showing the Non Matching donation.

Here is the Iif statement that works but she can no longer add to it so she had to drop off the year 1999.

Please bear in mind that I have no vba experience (even though I think this is where we will have to head).

Any advice would be greatly appreciated.

 

[MCantu]

when I get statements that are too long, I usually just start the filtering process earlier in the process with an extra query- if you are able to.

 

[Kathleen Badeau]

Ugly Iif statement

when I get statements that are too long, I usually just start the filtering process earlier in the process with an extra query- if you are able to.

You can't really do that here because the Calendar Yearly Matching and Nonmatching are in separate fields. In the long run she may have to enter the matching and non matching budgets in the trustee table as separate records rather than separate fields.

 

[The_Doc_Man]

First things first... there ain't no PRETTY IIF statements. They're ALL ugly.

I took a quick peek at the JPG. I wonder about the normalization of the tables from which you are working. If you have to go through that many gyrations, they probably are NOT normalized, and that will be a problem.

Rather than saying IF value = X then return value from Y, else if value = R then return value from S, else if value = A then return value from B, etc etc etc

consider building an intermediate table that has a pointer to X in one column and a pointer to Y in the other, then run a query (perhaps a two-layer query) that joins X to Y through the linking table.

Or perhaps a table that says if X (in column 1) and Y (in column 2) then link to Z (through column 3).

Trust me, SQL is NOT the place to put too many IIF statements. Redesign what you are doing. If necessary, toss the baby with the bathwater and start from first principles. Or perhaps what you REALLY want is a cross-tab query anyway. Look up that to see if it makes sense in your context.

Seriously, if you can't build the linking table I described, consider re-casting the data to another table structure. If necessary to preserve the original, IMPORT the data to a new structure and work with it in the new format that has all the stuff properly defined to allow you to just group by each year, month, match/non-match category, trustee, etc.

 

[Kathleen Badeau]

Ugly Iif's statement

First things first... there ain't no PRETTY IIF statements. They're ALL ugly.

I took a quick peek at the JPG. I wonder about the normalization of the tables from which you are working. If you have to go through that many gyrations, they probably are NOT normalized, and that will be a problem.

Rather than saying IF value = X then return value from Y, else if value = R then return value from S, else if value = A then return value from B, etc etc etc

consider building an intermediate table that has a pointer to X in one column and a pointer to Y in the other, then run a query (perhaps a two-layer query) that joins X to Y through the linking table.

Or perhaps a table that says if X (in column 1) and Y (in column 2) then link to Z (through column 3).

Trust me, SQL is NOT the place to put too many IIF statements. Redesign what you are doing. If necessary, toss the baby with the bathwater and start from first principles. Or perhaps what you REALLY want is a cross-tab query anyway. Look up that to see if it makes sense in your context.

Seriously, if you can't build the linking table I described, consider re-casting the data to another table structure. If necessary to preserve the original, IMPORT the data to a new structure and work with it in the new format that has all the stuff properly defined to allow you to just group by each year, month, match/non-match category, trustee, etc.

That was one of the possible solutions I had come up with. I think that it really is the only possible solution to my co-workers problem. Thanks for your help