NZ problem in a form using VBA

[bessej43]

I have a form that has a field called "SumOfTotal Days", it is populated by counting the number of days taken as leave. It rolls it up to one number. If a person does not take any leave then I want it to put a zero "0" in the SumOfTotal Days box. It is working fine when there is a value but not there is none.

The Form name is [Use for Lv Menu]
The populated field is [SumOfTotal Days]

Here is what I wrote but it is coming back with an error as follows; "You entered an expression that has no value".

Should I put the NZ statement in the query?

Private Sub Form_Open(Cancel As Integer)
Dim frm As Form, ctl As Control
Dim SumOfTotalDays As Single

' Return Form object variable pointing to the form.
Set frm = Forms![Use for LV Menu]
' Return Control object variable pointing to SumOfTotal Days.
Set ctl = frm![SumOfTotal Days]
' Choose result based on value of control.
varResult = Nz([SumOfTotal Days], "")
' Display result.
Me.[Use for LV Menu] = [SumOfTotal Days]
Me.Recordset.Edit
Me.Recordset![Use for LV Menu] = [SumOfTotal Days]
Me.Recordset.Update

Any help would be greatly appreicated.

 

[Rich]

Not sure I understand the logic, you have a field called SumOfTotalDays on a form called Use for LV Menu,
Me.[Use for LV Menu] is your form name, or is it a field name as well, what exactly are you trying to do, just display the Total days on the main form?

 

[jl39775]

Replace

varResult = Nz([SumOfTotal Days], "")


with

varResult = IIf(IsNull([SumOfTotal Days]), 0, [SumOfTotal Days])

James