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
|
|||
|
|||
#Error Count
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) |
#2
|
|||
|
|||
#Error Count
You can count the number of Null/empty values in a report or group section
with a control source of: =Sum(Abs(IsNull([FieldName]))) -- Duane Hookom Microsoft Access MVP "JT" wrote: 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) |
#3
|
|||
|
|||
#Error Count
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 '================================================= === JT wrote: 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) |
Thread Tools | |
Display Modes | |
|
|