Switch Issue

[skwilliams]

I have a query which is using a switch function to perform a calculation. The problem is the result is #Num! and I can't figure out why. This is the function.

MPP: Switch([CurPt]>0,[CurMin]/[CurPt],[CurOd]>0,[CurMin]/[CurOd],[CurMin]=0,0)

. I've attached the DB. The query in question is qryProdSupport1.

 

[plog]

The database you attached only has linked tables, not actual data, so I can't confirm anything. My guess is that your errors are division by 0 errors. That is, when [CurPt]=0 or [CurOd]=0 your query is unable to do these calculations:

[CurMin]/[CurPt] And [CurMin]/[CurOd].

Look at your data and see if those 2 fields are ever 0, if so that's probably the cause.

 

[MSAccessRookie]

Is it possible that you are getting the #Num! due to untested conditions?

The Switch Statement above will not have a defined value when the value of [CurMin] is <>0 and ( [CurPt] and [CurOd] are both >= 0).

I believe that the default value returned in those cases is NULL.

NOTE It looks like I am slow once again today, and that the idea of untested conditions has already been expressed. I think you need to look in that direction.

 

[skwilliams]

I've localized the tables and re-submitted. CurMin will never be zero unless both CurPt and CurOd are also zero.

 

[plog]

I've looked at your data and MSA and I were right.

 

[skwilliams]

CurMin will never have anything other than zero unless CurPt or CurOd also has a figure. So I told it that if CurMin=0 to make MPP zero. So there should be no division by zero error.