Count nulls in report

  • Thread starter Thread starter Marie
  • Start date Start date
M

Marie

Hi,

I have a report based on a table which gets its data from
a form (mainly option boxes). I can get the report to show
string values ("none","minor", etc)instead of the option
box numbers(1,2), but how can I count the null values
as "not recorded"?

Is it possible to do this purely in the report (leaving
the table exactly as it is)?

Many thanks,

Marie
 
Marie

?In a query, looking for a count of, say, primary keys where YourField
criteria is set to Is Null?
 
Hi Jeff,

Yes, sorry, the report is based on a count query. Here's
what I've got in the control source of the text box in the
report (my field name is 'activity'):

=IIf(IsNull([activity]),"Not Recorded",IIf([activity]
=1,"None",IIf([activity]=2,"Moderate")))

Which works, apart from the (Is Null) part (that is, the
None and Moderate parts return the right number but Not
Recorded just comes up as 0, when there are a number of
nulls).
Any ideas?

Many thanks,
Marie
 
Marie

What's the SQL look like for the query?

More info, please...

Jeff Boyce
<Access MVP>
 
Sorry Jeff, still a bit new to this!

Here is the SQL for the query the report is based on:

SELECT tblData.Activity, Count(tblData.Activity) AS
CountOfActivity
FROM tblData
GROUP BY tblData.Activity;

Would the Nz function be of use? Is so, where should I put
it (in qry or in report)?

Thanks,
MArie
 
Marie

I tested something this morning and came up with the following SQL statement
that generated the string "<nothing>" when the field held null. I also used
your IIF() function to alter a numeric value to a string.

See if it gives you any ideas in your situation...

SELECT
IIf(IsNull([FieldName]),"<nothing>",IIf([FieldName]=1,"one",[FieldName])) AS
Expr1, Count(Table1.FieldName) AS CountOfFieldName
FROM Table1
GROUP BY
IIf(IsNull([FieldName]),"<nothing>",IIf([FieldName]=1,"one",[FieldName]));
 
Back
Top