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
|
