johnnychow
Registered User.
- Local time
- Today, 06:18
- Joined
- Jul 28, 2011
- Messages
- 20
For...Next statement and convert string to currency
How can I combine three groups of 'For..Next" statements into one Statement and convert the "total" form string to currency. 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 = rst.GetString
Form_Summary.Controls("GEN" & i & "") = total <-------- convert to currency
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 = rst.GetString
Form_Summary.Controls("BLDG" & i & "") = total <-------- convert to currency
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]='MIS'" & _
"and [Offering Query].[Yearly]=" & yy & _
"And [Offering Query].[Monthly] =" & i
rst.Open sql, conn
total = rst.GetString
Form_Summary.Controls("MIS" & i & "") = total <-------- convert to currency
rst.Close
Next i
Set rst = Nothing
End Sub
How can I combine three groups of 'For..Next" statements into one Statement and convert the "total" form string to currency. 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 = rst.GetString
Form_Summary.Controls("GEN" & i & "") = total <-------- convert to currency
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 = rst.GetString
Form_Summary.Controls("BLDG" & i & "") = total <-------- convert to currency
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]='MIS'" & _
"and [Offering Query].[Yearly]=" & yy & _
"And [Offering Query].[Monthly] =" & i
rst.Open sql, conn
total = rst.GetString
Form_Summary.Controls("MIS" & i & "") = total <-------- convert to currency
rst.Close
Next i
Set rst = Nothing
End Sub
Last edited: