#num! error (1 Viewer)

[randallst]

Hi There,

I have been learning more about SQL from a colleague at work and starting to get the hang of it and seeing the benefits from using the normal query designer.

I have an issue where the following code works, but it returns the #num! error when the result is zero;

Round(Sum(IIf([Last Updated]>=[Period]![Start] And [Last Updated]<=[Period]![End] And [Incident Closed]=Yes And DateDiff("d",[Received Date],[Last Updated])<31,1,0))/Sum(IIf([Last Updated]>=[Period]![Start] And [Last Updated]<=[Period]![End],1,0))*100) AS [% Closed within 30 Days]

I have been reading about using Nz, and using another IIf statement, but each thread I've read I'm still returning #num!

Anyone got any ideas how I can get it to either return nothing or a zero?

Kind Regards
Stuart

P.S. Apologies for the long and probably unnecessary coding, I am trying to modify my predecessors database!

 

[plog]

That's too much. When you start trying to cram that much logic into one expression its time to make a function. Here's a link to help you get started:

https://www.fontstuff.com/vba/vbatut04.htm

With that said, my guess is either it doesn't like the Round around the SUM or its because you are doing division and its possible that you have a denominator that winds up as 0. Of course to be sure, you should write that function I mention so you can better understand what's happening at each point.

 

[Gasman]

Division by zero error?

 

[RogerCooper]

Hi There,
Round(Sum(IIf([Last Updated]>=[Period]![Start] And [Last Updated]<=[Period]![End] And [Incident Closed]=Yes And DateDiff("d",[Received Date],[Last Updated])<31,1,0))/Sum(IIf([Last Updated]>=[Period]![Start] And [Last Updated]<=[Period]![End],1,0))*100) AS [% Closed within 30 Days]

Rather than this approach, you should create a query which adds up the number of closed and total records and then have a query referencing the query to get the percentage.

 

[The_Doc_Man]

Agree with PLog and RogerCooper (and with Gasman's guess).

This logic is WAY too complex for a simple query as you wrote it. Either layer it (i.e. you'll have a query based on another query rather than a table) or write some sort of public function in a general module - which CAN be called from within a query. Break this up into pieces. Among other things, that IIF might be more suitable as a WHERE clause so that you don't ever consider something unless it meets the three criteria I see. Having said that, I have one more suggestion that looks not at the complexity or the syntax but the PURPOSE of what you are doing.

Looking at it closer, I see that you are using Sum of items which will only be 1 or 0, so you might consider that you are really just counting. So maybe you want to try something more like this:

X = DCount( "*", "[I]your table name goes here[/I]", "[Last Updated]>=[Period]![Start] And [Last Updated]<=[Period]![End] And [Incident Closed]=Yes And ( CLng([Received Date]) - CLng([Last Updated])<31" )

Y = DCount( "*", "[I]that same table name[/I]", "[Last Updated]>=[Period]![Start] And [Last Updated]<=[Period]![End]")

[Closed30Days] = Round( IIF( Y <> 0, 100*x/y, 0 ) )

The DCount( "*", ...) syntax just counts the number of items meeting the terms of the criteria string. So extract X as all of the items in the date range that are closed and were closed within 31 days. Then extract Y as all of the items in the same date range, closed or otherwise. Then compute your ratio and round that off. Since you didn't provide a rounding target, neither did I, so this would round it to the nearest integer. And that DateDiff("d",...) isn't really needed since the difference of two dates is already in days. So I used CLng to make them LONGs but you could have used ROUND or FIX equally well.

Further advice - this name is a total no-no: ... AS [% Closed within 30 Days]

Even though it is only an alias in a query, you REALLY REALLY REALLY don't want special characters in names and the spaces don't help either. Access has been known to hand out liberal doses of grief for using special characters.