Deviding by a value that could be 0 to get the percent 1

[mohebk]

Hi,
Using MS Access 2003.

I have a formula in a field that looks like this: =([Curr_Vis_Out]-[Prev_Vis_Out])/[Prev_Vis_Out] to get the percent of a change between two periods. I am getting a 0 if either fields is a zero value. How can I program this to accommodate when the zero is in the denominator or the numerator. I think it should work is way: if the [Curr_Vis_Out] = 0 or null the result should be (-100%) but if the [Prev_Vis_Out] = 0 or null the result should be 100%.

Thanks

Mo

 

[dhookom]

I'm not sure what you want if they are both Null and/or zero.

=IIF(Nz([Prev_Vis_Out],0) = 0, 1, (Nz([Curr_Vis_Out],0)-[Prev_Vis_Out])/[Prev_Vis_Out])

Duane
Hook'D on Access
MS Access MVP

 

[mohebk]

This is exactly what I was looking for. The only thing left is actually an answer to your question, if both are null or zero I would like the result be 0%. Where can insert that.

Thanks

Mo

 

[dhookom]

I hate creating nested IIf()s since you end up store business rules in expressions. I would create a small user-defined function that would accept the two values and return a result:

Public Function GetPctChange(varPrev As Variant, varCur As Variant) As Double
If Nz(varPrev, 0) = 0 Then
   If Nz(varCur, 0) = 0 Then
      GetPctChange = 0
    Else
      GetPctChange = 1
   End If
 Else
   GetPctChange = (Nz(varCur, 0) - varPrev) / varPrev
End If

End Function

You could then use this in a query or contol source:

=GetPctChange([Prev_Vis_Out], [Curr_Vis_Out])

Duane
Hook'D on Access
MS Access MVP

 

[mohebk]

The second post was the perfect approach. Thanks a lot for your help.

Mo