after run follow code can not add up

[johnnychow]

After I ran the follow code, some text box are blank because there are no records but I want to set it in $0.00 in order to be a added text box, I tried (if isnull("text box") then Me.text box=Ccur(0)), but it doesn't work. Why? Somebody can help????Thank you!!!!!!!!!!!

Private Sub CombYear_AfterUpdate()
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim total
Dim yy As Integer

yy = Me.CombYear

For i = 1 To 12
Set conn = CurrentProject.Connection
sql = "select sum([Offering Query].[Among]) from [Offering Query] where [Offering Query].[FundCode]='GEN'" & _
"and [Offering Query].[Yearly]=" & yy & _
"And [Offering Query].[Monthly] =" & i

rst.Open sql, conn
total = Format(rst.GetString, "Currency")
Form_Summary.Controls("GEN" & i & "") = total

rst.Close

Next i

For i = 1 To 12
Set conn = CurrentProject.Connection
sql = "select sum([Offering Query].[Among]) from [Offering Query] where [Offering Query].[FundCode]='BLDG'" & _
"and [Offering Query].[Yearly]=" & yy & _
"And [Offering Query].[Monthly] =" & i

rst.Open sql, conn
total = Format(rst.GetString, "Currency")
Form_Summary.Controls("BLDG" & i & "") = total

rst.Close

Next i

Set rst = Nothing

setzero

End Sub




Sub setzero()

If IsNull(Form_Summary.Controls("GEN1")) Then
Me.GEN1.DefaultValue = CCur(0) <-----stuck on here
End If

Me.Text69 = CCur(Me.GEN1) + CCur(Me.BLDG1) <----GEN1 is blank and BLDG1 is not blank
End Sub

 

[apr pillai]

Try the sub-routine with the following statements:

Sub setzero()

Me.GEN1.Value = CCur(Nz(Me.GEN1.Value, 0))

Me.TEXT69.Value = CCur(Nz(Me.GEN1, 0)) + CCur(Nz(Me.BLDG1, 0))
End Sub

The reference to the DefaultValue property is wrong in the statement: Me.GEN1.DefaultValue = CCur(0).

The testing for Null and assigning Zero value can be done as shown above as part of the expression itself with the function Nz(), rather than validating with IF...Then statement.

 

[johnnychow]

Try the sub-routine with the following statements:

Sub setzero()
 
Me.GEN1.Value = CCur(Nz(Me.GEN1.Value, 0))
 
Me.TEXT69.Value = CCur(Nz(Me.GEN1, 0)) + CCur(Nz(Me.BLDG1, 0))
End Sub

The reference to the DefaultValue property is wrong in the statement: Me.GEN1.DefaultValue = CCur(0).

The testing for Null and assigning Zero value can be done as shown above as part of the expression itself with the function Nz(), rather than validating with IF...Then statement.

Not work still stay null and uncalculable, can someone give me more help?