Problem using < in the DSum and DCount functions on a report

[Petrosdidymos]

I have a report that I use and one of the cells is using this string to return a percentage:



=(DCount("[Actual Response Time]","Service Calls Last Week Response Time Measured","[Actual Response Time]<120")/DCount("[Actual Response Time]","Service Calls Last Week Response Time Measured","[Actual Response Time]>0"))

The report stop working and started returning "Error#" in all of the cells with similar formulas in it.

I played around with it yesterday and it appears that the < and > functions are no longer working as a criteria.

Does anyone have any experience with this or suggestions on how to make this work?

Thanks.

[Dal Jeanis]

First, put braces around [Service Calls Last Week Response Time Measured] everywhere it occurs. Then see what happens.

Second, you might consider ensuring that the denominator is always greater than zero. It's uglier than your curent code, but it's something like this -

=((DCount("[Actual Response Time]","[Service Calls Last Week Response Time Measured]","[Actual Response Time]<120"))/(IIF(DCount("[Actual Response Time]","[Service Calls Last Week Response Time Measured]","[Actual Response Time]>0")>0,DCount("[Actual Response Time]","[Service Calls Last Week Response Time Measured]","[Actual Response Time]>0"),1))

or you could implement and use a public function to pick the greater of two integers:

Public Function GreaterInt(Int1 As Integer, Int2 As Integer) AS Integer
  If int1 > int2 Then 
     GreaterInt = Int1
  Else
     GreaterInt = Int2
  End if
End Function  
=((DCount("[Actual Response Time]","[Service Calls Last Week Response Time Measured]","[Actual Response Time]<120"))/GreaterInt(DCount("[Actual Response Time]","[Service Calls Last Week Response Time Measured]","[Actual Response Time]>0"),1)