Switch function problem

[skwilliams]

I'm trying to get the Minutes per part using the switch function or minutes per order if parts are zero.

Here's the function:

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

MPP is filling with #Num! if CurPt, CurOd, and CurMn =0 or CurPt and CurOd =0 and CurMn >0. MPP is null if all the fields are >0.

Can anyone see what's wrong with the switch function?

 

[MSAccessRookie]

I'm trying to get the Minutes per part using the switch function or minutes per order if parts are zero.

Here's the function:

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

MPP is filling with #Num! if CurPt, CurOd, and CurMn =0 or CurPt and CurOd =0 and CurMn >0. MPP is null if all the fields are >0.

Can anyone see what's wrong with the switch function?

The error is likely occurring because the your Switch() Function results in a Null Value (Check the link below for possible causes). You can also try using the Nz() Function to handle Null results if you are unable to deal with them in any other way.

http://office.microsoft.com/en-us/access-help/switch-function-HA001228918.aspx

 

[Isskint]

As MSAccessRookie says, it could be a null value or the result does not fit the numeric datatype (Integer, Double etc) for that field. The #Num! error value means that the value in the field is too large (either positively or negatively) to be stored in the field, based on the field's DataType or FieldSize property setting.

Could you post us some test values? i Wonder if your switch equation captures all possible combinations? Also there is the issue of logical progression through your equation. This AND that AND whatever can evaluate differently to (This AND that) AND whatever orThis AND (that AND whatever) etc. Brackets are SO important in equations

 

[skwilliams]

Here's the updated switch function. This should contain all possible combinations.

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

Seems to evaluate correctly if all values are >0 but not with other combinations.

Attached is a pdf file of results from the above switch.

 

[MSAccessRookie]

Here's the updated switch function. This should contain all possible combinations.

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

Seems to evaluate correctly if all values are >0 but not with other combinations.

Attached is a pdf file of results from the above switch.

Perhaps you can set a default for other cases. If you were using IF, I would suggest ELSE. With a Switch, the same thing can be accomplished by having the final case be a case that is always true. For instance, if the final switch is (1=1, -1), then any remaining instances would have a value of -1. You can use this to find out what cases you may have missed.

-- Rookie