Can't set the null or "" text box to be a calculable text box

[johnnychow]

Hi! I want to fill up all the text box, if the value are null or "" then give them 0 value, in order to make it to be a calculable text box, I tried many methods but failed, would someone correct it, please!!!!!!!!. Maybe the red part are the problem??


Private Sub CombYear_AfterUpdate()
Dim TxtFundCode As String
Dim i As Integer

For i = 1 To 12

TxtFundCode = "GEN"
Me.Controls(TxtFundCode & i & "") = GetTotal(TxtFundCode, Me.CombYear, i)
TxtFundCode = "MIS"
Me.Controls(TxtFundCode & i & "") = GetTotal(TxtFundCode, Me.CombYear, i)
TxtFundCode = "BLDG"
Me.Controls(TxtFundCode & i & "") = GetTotal(TxtFundCode, Me.CombYear, i)
TxtFundCode = "THKG"
Me.Controls(TxtFundCode & i & "") = GetTotal(TxtFundCode, Me.CombYear, i)
TxtFundCode = "OTH"
Me.Controls(TxtFundCode & i & "") = GetTotal(TxtFundCode, Me.CombYear, i)
Next i
End Sub


Private Function GetTotal(TxtFundCode As String, YY As Integer, iMonth As Integer) As Variant
Dim rst As New ADODB.Recordset
Dim sql As String
Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection
sql = "select sum([Offering Query].[Among]) from [Offering Query] where [Offering Query].[FundCode]='" & TxtFundCode & "'" & _
"and [Offering Query].[Yearly]=" & YY & _
"And [Offering Query].[Monthly] =" & iMonth

rst.Open sql, conn

'GetTotal = Nz(Format(rst.GetString, "Currency"), 0) <---------------------Failed to set 0
'GetTotal = Format(IIf(rst.GetString = "", 0, rst.GetString), "Currency") <------error
'GetTotal = Format(Nz(rst.GetString, 0), "Currency") <---------------------Failed to set 0
'GetTotal = CCur(Nz(Me.GEN1.Value, 0)) <-----------------------------------Failed to set 0
'GetTotal = Nz(rst.GetString, 0) <------------------------------------------------Failed to set 0

rst.Close

End Function