• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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.
Screen Shot
Thanks,
Sandesh.
0
Sandesh555
Asked:
Sandesh555
  • 3
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
0
 
mbizupCommented:
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.
0
 
mbizupCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Sandesh555Author Commented:
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.
0
 
Sandesh555Author Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
this is a basic syntax


IIf([ShipRegion] Is Null And [ShipCountry]="Mexico","Yes","No")
0
 
Rey Obrero (Capricorn1)Commented:
better if you can upload a strip down version of your db
0
 
Sandesh555Author Commented:
Let me try that.. Given me some time... I will post back. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now