Nested IIf and IsNull not working
- Thread starter moishy
- Start date
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.
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.
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
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
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.
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.
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?
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?
Absolument!
So what you should do now is put all three criteria into one like so:
Before I advise you about the IIF() part, why are you needing to convert Null to ""?
So what you should do now is put all three criteria into one like so:
Code:
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 ""?
