Nested IIf and IsNull not working

[moishy]

Can someone please take a look at qryDonations in the attached mdb and explain why it doesn't work.

 

[Trevor G]

What are you trying to achieve?

 

[moishy]

Create a list of all donors and their donations for the last 3 years.

 

[Trevor G]

Place this in a new query.

SELECT tblDonorDetails.Surname, tblDonorDetails.[First Name], tblDonations.Amount, tblDonations.Currency, tblDonations.Year, tblDonations.Month
FROM tblDonorDetails INNER JOIN tblDonations ON tblDonorDetails.ID = tblDonations.ID
WHERE (((tblDonations.Year)>=2009));

 

[moishy]

Sorry for the late response, we're experiencing some trouble with our servers. Thanks for your time and trouble in trying to help.
Regarding the issue at hand, your suggestion will only give me a list of donations for the year 2009 or earlier, what I need, and I'm sorry if I wasn't clear enough initially, is a list of all donors in the database and three columns for last year two years ago and three years ago, please take into account the following points: a. this has to work for this year and many more to come (so hardcoding a year, as you suggested won't work). b. in case of no donation I still need the record of the donor (the three columns should be empty).

Thanks again for your time, and I'm sorry if I didn't explain myself well enough.

 

[vbaInet]

So can you post the SQL statement of the query you were hoping would work?

 

[moishy]

You will find it in the sample mdb attached to the question. here it is for your convenience:

SELECT DISTINCT tblDonorDetails.Surname, tblDonorDetails.[First Name], tblDonations.Amount, tblDonations.Currency, tblDonations.Year, tblDonations.Month, IIf(IsNull(qryThisYear!Amount),"",qryThisYear!Amount & qryThisYear!Currency) AS [This Year], IIf(IsNull(qryLastYear!Amount),"",qryLastYear!Amount & qryLastYear!Currency) AS [Last Year], IIf(IsNull(qryTwoYearsAgo!Amount),"",qryTwoYearsAgo!Amount & qryTwoYearsAgo!Currency) AS [Two Years Ago], IIf(IsNull(qryThreeYearsAgo!Amount),"",qryThreeYearsAgo!Amount & qryThreeYearsAgo!Currency) AS [Three Years Ago]
FROM tblDonations, (((tblDonorDetails INNER JOIN qryThisYear ON tblDonorDetails.ID = qryThisYear.ID) INNER JOIN qryLastYear ON tblDonorDetails.ID = qryLastYear.ID) INNER JOIN qryTwoYearsAgo ON tblDonorDetails.ID = qryTwoYearsAgo.ID) INNER JOIN qryThreeYearsAgo ON tblDonorDetails.ID = qryThreeYearsAgo.ID;

where qryLastYear is:

SELECT tblDonations.ID, tblDonations.Amount, tblDonations.Currency, tblDonations.Year AS [Last Year]
FROM tblDonations
WHERE (((tblDonations.Year)=Year(Now())-1));

and qryTwoYearsAgo is:

SELECT tblDonations.ID, tblDonations.Amount, tblDonations.Currency, tblDonations.Year AS [Two Years Ago]
FROM tblDonations
WHERE (((tblDonations.Year)=Right(Date(),4)-"2"));

and qryThreeYearsAgo is:

SELECT tblDonations.ID, tblDonations.Amount, tblDonations.Currency, tblDonations.Year AS [Three Years Ago]
FROM tblDonations
WHERE (((tblDonations.Year)=Right(Date(),4)-"3"));

 

[vbaInet]

Yeah I know it's on the mdb but we like to see it pasted on here. Why would I download a db, open it, trust it and open the query in design view just to see what you've done. Too much effort.

So let's get the criteria sorted out first before we look at consolidating all three queries. You need to use the DateAdd() function. Have a look here:

http://www.techonthenet.com/access/functions/date/dateadd.php

 

[moishy]

Sorry I'm dense but where is this going?

 

[vbaInet]

I just had another look at your query and noticed that you actually have a Year field. So first of all Year should not be used as a field name. As you know, it is also a VBA function and it will cause conflicts in the near future.

Secondly, the criteria you wrote isn't properly formed.

WHERE (((tblDonations.Year)=Year(Now())-1));
This is fine. Actually, not completely - you should be using Date() instead of Now()

WHERE (((tblDonations.Year)=Right(Date(),4)-"2"));
WHERE (((tblDonations.Year)=Right(Date(),4)-"3"));
This may work but it's not properly written. Why don't you do it the same way as above.

Like I mentioned previously, once we get these things straightened out I will tell you how to write one query to consolidate your three queries.

 

[moishy]

Ok, thanks for the comments.
I changed Year to CivilYear, and the criteria is
WHERE (((tblDonations.CivilYear)=Year(Date())-1));
WHERE (((tblDonations.CivilYear)=Year(Date())-2));
WHERE (((tblDonations.CivilYear)=Year(Date())-3));

Is this what you had in mind?

 

[vbaInet]

Absolument!

So what you should do now is put all three criteria into one like so:

WHERE (CivilYear = Year(Date())-1) OR (tblDonations.CivilYear = Year(Date())-2) OR (tblDonations.CivilYear = Year(Date())-3);

Before I advise you about the IIF() part, why are you needing to convert Null to ""?

 

[moishy]

In case of no donation I still need the record of the donor (the fields for the past three years should be empty (hence "").

 

[vbaInet]

Null is empty anyway, isn't that sufficient?

 

[moishy]

Thanks for your help.

 

[vbaInet]

You're welcome!