If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Using IIF statement in Memo Field
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 |
#3
|
|||
|
|||
Using IIF statement in Memo Field
Thank you, Linda. That worked. I appreciate your help.
Jeff |
#4
|
|||
|
|||
Using IIF statement in Memo Field
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 |
#5
|
|||
|
|||
Using IIF statement in Memo Field
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 |
#6
|
|||
|
|||
Using IIF statement in Memo Field
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 |
Thread Tools | |
Display Modes | |
|
|