#Error Count

[JT]

I have a query which converts a date format from YYYYMMDD to MM/DD/YYYY.
When there is no date, the query returns a blank. When I use this query in
my report, the report field returns "#Error" when the date is blank. I
understand why, but what I want to do is to count the number of #Error
occurrences. I have tried =SUM(IIF([NEED] ="#Error#",1,0)) and
=SUM(IIF([NEED_DATE] < 0,1,0)) and neither work. Any ideas?? (Note that
NEED is the formatted version of NEED_DATE)

 

[Duane Hookom]

You can count the number of Null/empty values in a report or group section
with a control source of:
=Sum(Abs(IsNull([FieldName])))

 

[John Spencer]

Fix it so that your query handles the conversion properly.

Perhaps using an expression like the following to convert the field to
date or to a null value if the field cannot be properly converted.

IIF(IsDate(Format([NEED],"@@@@-@@-@@")),CDate(Format([NEED],"@@@@-@@-@@")),Null)

Now you can count the nulls in your report if that is needed, by using a
control with this expression as the source

= Abs(Sum([Need] is Null))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================