Using IIF statement in Memo Field

Hi,

I'm fairly new to Access, and I need to write a query which will check a memo field to see if it is null, then display "n" (for not having data in the field) if it is null or "y" (if there is data in the field). I'm having trouble using the IIF statement correctly. Every time I try to run the field it says "You tried to execute a query that does not include the specified expression as part of an aggregate function" Needless to say, I have been unable to figure out what the error is and how to fix it.

This is my SQL statement:

SELECT JobProfile.Title, JobProfile.JobCode, IIf(Nz([Elaboration]," ")=" ","n","y") AS Expr1
FROM JobProfile LEFT JOIN EssentialFunction ON JobProfile.JobCode = EssentialFunction.JobCode
GROUP BY JobProfile.Title, JobProfile.JobCode
HAVING (((IIf(Nz([Elaboration]," ")=" ","n","y"))=True));

If I get this working, I would like to include more memo fields and run the same expression on them as well.

Thanks in advance for any help.

Jeff

Every field in your Select Clause must also be in the Group By Clause. Add
[Elaboration] to the Group By.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"J Cunningham" wrote in message
...
Hi,

I'm fairly new to Access, and I need to write a query which will check a
memo field to see if it is null, then display "n" (for not having data in
the field) if it is null or "y" (if there is data in the field). I'm having
trouble using the IIF statement correctly. Every time I try to run the
field it says "You tried to execute a query that does not include the
specified expression as part of an aggregate function" Needless to say, I
have been unable to figure out what the error is and how to fix it.

This is my SQL statement:

SELECT JobProfile.Title, JobProfile.JobCode, IIf(Nz([Elaboration]," ")="
","n","y") AS Expr1
FROM JobProfile LEFT JOIN EssentialFunction ON JobProfile.JobCode =
EssentialFunction.JobCode
GROUP BY JobProfile.Title, JobProfile.JobCode
HAVING (((IIf(Nz([Elaboration]," ")=" ","n","y"))=True));

If I get this working, I would like to include more memo fields and run
the same expression on them as well.

Thanks in advance for any help.

Jeff

Thank you, Linda. That worked. I appreciate your help.

Jeff

I'm glad it worked.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"J Cunningham" wrote in message
...
Thank you, Linda. That worked. I appreciate your help.

Jeff

In addition to what LYNN said, I would add to his advice.

I would drop the NZ and just test

IIF([Elaboration] is Null, "N", "Y")

Plus it looks as if you are just trying to return rows where [Elaboration] is
null so I would change the whole SQL query to

SELECT JobProfile.Title, JobProfile.JobCode
FROM JobProfile LEFT JOIN EssentialFunction
ON JobProfile.JobCode = EssentialFunction.JobCode
WHERE [Elaboration] Is Not Null
GROUP BY JobProfile.Title, JobProfile.JobCode

And it could be even simpler than this, since you really don't need any
aggregate or grouping based on your originally posted SQL statement, you can use
the DISTINCT clause to return just the rows you want

SELECT DISTINCT JobProfile.Title, JobProfile.JobCode
FROM JobProfile LEFT JOIN EssentialFunction
ON JobProfile.JobCode = EssentialFunction.JobCode
WHERE [Elaboration] Is Not Null


J Cunningham wrote:

Thank you, Linda. That worked. I appreciate your help.

Jeff

Every field in your Select Clause must also be in the Group By Clause. Add
[Elaboration] to the Group By.

Last I checked (A2K, Jet Sr7), you could not reliably group by memo fields.

(david)

"Lynn Trapp" wrote in message
...
Every field in your Select Clause must also be in the Group By Clause. Add
[Elaboration] to the Group By.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"J Cunningham" wrote in message
...
Hi,

I'm fairly new to Access, and I need to write a query which will check a
memo field to see if it is null, then display "n" (for not having data in
the field) if it is null or "y" (if there is data in the field). I'm
having
trouble using the IIF statement correctly. Every time I try to run the
field it says "You tried to execute a query that does not include the
specified expression as part of an aggregate function" Needless to say, I
have been unable to figure out what the error is and how to fix it.

This is my SQL statement:

SELECT JobProfile.Title, JobProfile.JobCode, IIf(Nz([Elaboration]," ")="
","n","y") AS Expr1
FROM JobProfile LEFT JOIN EssentialFunction ON JobProfile.JobCode =
EssentialFunction.JobCode
GROUP BY JobProfile.Title, JobProfile.JobCode
HAVING (((IIf(Nz([Elaboration]," ")=" ","n","y"))=True));

If I get this working, I would like to include more memo fields and run
the same expression on them as well.

Thanks in advance for any help.

Jeff