Calculated Field values question

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

I have a form with 4 calculated fields (F1,F2,F3,F4) that get their values
from subforms, each calculated field is on a separate tab page. Depending on
user selections, not all tab pages are visible but at least one tab page
will be visible. I would like to total all four calculated fields and
display the total in a fifth calculated field (Msgbox below in place of F5).

I thought this would be straight forward by summing F1:F4 using the NZ
function. (Got an error.) It turns out that if any of the four calculated
fields is not visible and therefore has no value, the VARTYPE is "object".
But if a field has a value then the VARTYPE is "currency" as expected. The
following code seems to work, but can someone explain what is going on with
the two different VARTYPEs and, is there a easier way to get zeros for
calculated fields other than using a default value which I prefer not to do?

MsgBox "Total: " _
& IIf(VarType(Me.F1) = 9, 0, Me.F1) _
+ IIf(VarType(Me.F2) = 9, 0, Me.F2) _
+ IIf(VarType(Me.F3) = 9, 0, Me.F3) _
+ IIf(VarType(Me.F4) = 9, 0, Me.F4)
 
Back
Top