How to do a query on data type memo?

[gina farrow]

I keep getting a message," syntax in your sub query is incorrect". For the following code:

SELECT IIf(IsNull([Answer]);"No";"Yes") AS [Answered?]; Count(IIf(IsNull([Answer]);"No";"Yes")) AS Qtd
FROM Motivos
GROUP BY IIf(IsNull([Answer]);"No";"Yes");

My memo data type is "answer". I need to know how many users answered in the memo(answer)data type, and how many users didn't. And I need to also show the users who wrote something on the memo data type.

[Stewart Ross]

There is a repeated error in your IIF statement, where you are separating the arguments with semi-colons instead commas:

                                v    v 
incorrect - IIf(IsNull([Answer]);"No";"Yes")
                                ^    ^
 
correct   - IIf(IsNull([Answer]),"No","Yes")
 

You are also repeating the same problem within the SQL statement itself, where you are using the semi-colon to separate fields instead of the comma.

Your first IIF groups the results into whether or not an answer has been provided. Accordingly there will be two rows returned, one in which the calculated field you have called [Answered?] will take the value "Yes", and the other "No". Beside it you will then have a count of the number of rows.

I can simplfy the SQL for this a little and use the SQL 'IS NULL' statement in place of the IsNUll() function as follows:

SELECT 
  IIf([Answer] IS NULL, "No", "Yes") AS [Answered?], 
  Count([Answer]) AS [No of Answers]
FROM 
  Motivos
GROUP BY 
  IIf([Answer] IS NULL, "No", "Yes");

This will not give you an overall total because of the "yes"/"no" grouping. To get all of what you want in one row you could use something like this:

SELECT 
  Count([Answer]) AS [Total Rows], 
  Sum(IIf([Answer] Is Null,0,1)) AS [Answered], 
  Sum(IIf([Answer] Is Null,1,0)) AS [Not Answered]
FROM Motivos;

-Stewart

[gina farrow]

Wow! Your good!