asked on

AND operator usage with IIF

Hi Team,

Using the link given below I am tring to use the AND operator in the IIF statement in Access query. but it does not seem to work. Is their a different way to using the AND opeartor in Access 2007.

http://windowssecrets.com/forums/showthread.php/13736-AND-operator-within-IIF-(Access-97)

Please advise. I have also attached the screen shot of the error I get when I try to run the code.

Here is the Query.
SELECT tblPCCMain.PolicyRef, IIf(IsNull(Last([tblEmails&Comments].[Sent Date])),0,CDate(Format(Last([tblEmails&Comments].[Sent Date]),"mm/dd/yyyy"))) AS [Email Date], IIf(IsNull(Last(tblPCCCommentsOld.OldComments)),0,CDate(Left(Last(tblPCCCommentsOld.OldComments),InStr(1,Last(tblPCCCommentsOld.OldComments)," ")-1))) AS [Comment Date], IIf([Email Date]>[Comment Date],[Email Date],[Comment Date]) AS [Date], IIf([Date]=0,'',CDate(Format(Now(),"mm/dd/yyyy"))-[Date]) AS Diff, First(qryPCCMain.Aging) AS Aging, First(qryPCCMain.[Due date]) AS [FirstOfDue date],IIF([Aging]='Not Yet Due','Not Yet due',IIF([Diff]='' AND [Due date]<>'Not Found','Need Update','')) as [Email Aging]
FROM ((tblPCCMain LEFT JOIN [tblEmails&Comments] ON tblPCCMain.PolicyRef = [tblEmails&Comments].[Policy Reference]) LEFT JOIN tblPCCCommentsOld ON tblPCCMain.PolicyRef = tblPCCCommentsOld.PolicyNumber) LEFT JOIN qryPCCMain ON tblPCCMain.PolicyRef = qryPCCMain.PolicyRef
GROUP BY tblPCCMain.PolicyRef;

and here is teh error I am getting.

Thanks,
Sandesh.

Rey Obrero (Capricorn1)

open your query in design view

select Group By or whatever aggregate function is appropriate in the Total row, for all the columns you have in your query

mbizup

When you use aggregate functions such as FIRST, everything in your SELECT statement needs to appear either in an aggregate function or the GROUP BY clause.

Try placing your entire iif statement in the GROUP BY clause.

mbizup

Fwiw, there is no difference between access 97 and 2007 on this respect. I'm guessing that adding a new column caused the problem.

Any new columns should be in aggregate functions or in the GROUP BY clause.

Sandesh555

ASKER

Hi,

I have check the Goup By option and this is put under 'Expression'. Now I tried to do some testing and the Query works perfectly if I remove the AND operator from the query. It gives this error when I put back the AND operator. I have done this several times now.

Thanks,
Sandesh.

Sandesh555

ASKER

Hi Team,

I am also ok if you share with me a sample Query on how to solve the same using a sample data.

I only want to know how to solve this techinically. Resolving the attached query is given only for example.

thanks,
sandesh.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

better if you can upload a strip down version of your db

Sandesh555

ASKER

Let me try that.. Given me some time... I will post back. :)