For...Next statement and convert strint to currency

J

johnnychow

Registered User.
Local time
Today, 06:08
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
 
Last edited:
Try this:

Code: 
Private Sub CombYear_AfterUpdate()

    Dim conn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim sql As String
    Dim Mtotal, Btotal, Gtotal As Currency
    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
        Gtotal = Format(rst.GetString, "Currency")
        Form_Summary.Controls("GEN" & i & "") = Gtotal
        rst.Close
    
        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
        Btotal = Format(rst.GetString, "Currency")
        Form_Summary.Controls("BLDG" & i & "") = Btotal
    
        rst.Close
    
        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
        Mtotal = Format(rst.GetString, "Currency")
        Form_Summary.Controls("MIS" & i & "") = Mtotal
    
        rst.Close

    Next i

    Set rst = Nothing
End Sub
 
Plus don't use Form_Summary, use Me. if you're calling the form in its module or
Forms("Summary") if you're calling it from a different module.
 
r.harrison said:
Try this:

Code: 
Private Sub CombYear_AfterUpdate()
 
    Dim conn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim sql As String
    Dim Mtotal, Btotal, Gtotal As Currency
    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
        Gtotal = Format(rst.GetString, "Currency")
        Form_Summary.Controls("GEN" & i & "") = Gtotal
        rst.Close
 
        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
        Btotal = Format(rst.GetString, "Currency")
        Form_Summary.Controls("BLDG" & i & "") = Btotal
 
        rst.Close
 
        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
        Mtotal = Format(rst.GetString, "Currency")
        Form_Summary.Controls("MIS" & i & "") = Mtotal
 
        rst.Close
 
    Next i
 
    Set rst = Nothing
End Sub
Click to expand...


I need one more help on this code. If the controls are null, I want to put $0.00 on the Null controls because I will add all the controls value up at the end. If the controls are null will cause the error. thank you!!!!!!!!!!!
 
Possible solution would be to catch a null value and then make it 0;

Code: 
rst.Open sql, conn
if isnull(rst.GetString) then 
Gtotal=0
Else
Gtotal = Format(rst.GetString, "Currency")
End If
Form_Summary.Controls("GEN" & i & "") = Gtotal
rst.Close
 
Not Nz()?

Anyway, we're dealing with a recordset here so a RecordCount is what you're after.
 
Just another thought. To avoid repeating code, you could use a function which will reduce the amount of coding.

Code: 
Private Sub CombYear_AfterUpdate()

    For i = 1 To 12
        
        strFundCode = "GEN"
        Me.Controls(strFundCode & i & "") = GetTotal(strFundCode, Me.CombYear, i)
        strFundCode = "MIS"
        Me.Controls(strFundCode & i & "") = GetTotal(strFundCode, Me.CombYear, i)
        strFundCode = "BLDG"
        Me.Controls(strFundCode & i & "") = GetTotal(strFundCode, Me.CombYear, i)

    Next i

End Sub

Private Function GetTotal(strFundCode As String, YY As Integer, iMonth As Integer) As Currency

    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]='" & strFundCode & "'" & _
            "and [Offering Query].[Yearly]=" & YY & _
            "And [Offering Query].[Monthly] =" & iMonth
    
    rst.Open sql, conn
    GetTotal = Nz(Format(rst.GetString, "Currency"),0)
    
    rst.Close
    
End Function

I haven't tested the code, but it should work fine.
 
Last edited:
I tried many methods but never set $o.oo value on the null text box, how can I use the Null value text box as a calculable text box. Thank you!!!!!!!!!!!!!


Private Sub CombYear_AfterUpdate()

Dim strFundCode As String
Dim i As Integer

For i = 1 To 12

strFundCode = "GEN"
Me.Controls(strFundCode & i & "") = GetTotal(strFundCode, Me.CombYear, i) <-----can someone tell me, why need "" on this code
strFundCode = "MIS"
Me.Controls(strFundCode & i & "") = GetTotal(strFundCode, Me.CombYear, i)
strFundCode = "BLDG"
Me.Controls(strFundCode & i & "") = GetTotal(strFundCode, Me.CombYear, i)
Next i

End Sub



Private Function GetTotal(strFundCode 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]='" & strFundCode & "'" & _
"and [Offering Query].[Yearly]=" & YY & _
"And [Offering Query].[Monthly] =" & iMonth

rst.Open sql, conn

GetTotal = Nz(Format(rst.GetString, "Currency"), 0) <----Never get $0.00 value, even those text box which can get value but in string can't calculate, I have to use CCur() on expression builder to convert it.

rst.Close

End Function
 
Last edited:
Sorry, forgot how to format this correctly

Try replacing:

Code: 
GetTotal = Nz(Format(rst.GetString, "Currency"), 0)

With

Code: 
GetTotal =Format(Nz(rst.GetString, 0), "Currency")

and the & "" doesn't appear to have any relevance
 
r.harrison said:
Sorry, forgot how to format this correctly

Try replacing:

Code: 
GetTotal = Nz(Format(rst.GetString, "Currency"), 0)

With

Code: 
GetTotal =Format(Nz(rst.GetString, 0), "Currency")

and the & "" doesn't appear to have any relevance
Click to expand...


Hi: Both are not work, just work on recordset with value not null.
 
Hi,

I think I've solved it. When the rst.getstring returns a null value, is it actually null, or is it ""

If it's "" then use

GetTotal = Format(IIf(rst.GetString = "", 0, rst.GetString), "Currency")

Other than that I'm running out of ideas!
 
r.harrison said:
Hi,

I think I've solved it. When the rst.getstring returns a null value, is it actually null, or is it ""

If it's "" then use

GetTotal = Format(IIf(rst.GetString = "", 0, rst.GetString), "Currency")

Other than that I'm running out of ideas!
Click to expand...


this time has a error message: Run time error '3021': Ether BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record.
 
It seems like sometimes the function returns "" and other times returns Null. Give this a try:


GetTotal = Format(IIf(rst.GetString = "" Or IsNull(rst.GetString), 0, rst.GetString), "Currency")

Fingers crossed!
 
Hi, r.harrison thank you for your help, it is still not work, but I did some test, the result are enlightened, maybe can help you to solve my problem. thank!!!!!!!!!!
I think it can't check the text box "" or not"" within the opened recordset, check it after the procedure done and do it on the other procedure, I am not sure, I am a new guy on programming, just I Thought. thank you once again!!!!!!!!!!!!!




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

For i = 1 To 12

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

' Me.GEN1 = 0 <<<<----------work fine

' Me.GEN1 = Nz(Me.GEN1, 0) <<<<--------------txet box can't set 0 but blank

' Me.Controls("GEN1") = IIf(Me.Controls("GEN1"), 0, Me.GEN1) <<<<-------------------type mismatch

'If Me.GEN1 = "" Then <<<<<-------------text box can't set 0 but blank
'Me.GEN1 = 0
'End If

'If Me.GEN1 = Null Then <<<<<-------------text box can't set 0 but blank
'Me.GEN1 = 0
'End If

'If IsNull(Me.GEN1) Then <<<<<-------------text box can't set 0 but blank
'Me.GEN1 = 0
'End If

'If Me.GEN1 < 0 Then <<<<<-------------text box can't set 0 but blank
'Me.GEN1 = 0
'End If

'If Me.GEN1 >= 0 Then <<<<<-------------work fine
'Me.GEN1 = 0
'End If

End Sub



Private Function GetTotal(strFundCode 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]='" & strFundCode & "'" & _
"and [Offering Query].[Yearly]=" & YY & _
"And [Offering Query].[Monthly] =" & iMonth

rst.Open sql, conn

'GetTotal = Nz(Format(rst.GetString, "Currency"), 0) <----------------work fine but blank text box stay blank
'GetTotal = Format(IIf(rst.GetString = "" Or IsNull(rst.GetString), 0, rst.GetString), "Currency") <<<<<<<<------------------error '3021'
'GetTotal = Format(IIf(IsNull(rst.GetString), 0, rst.GetString), "Currency") <<<<----------------------------------------------error'3021'
'GetTotal = Format(IIf(rst.GetString = "", 0, rst.GetString), "Currency") <<<<----------------------------------------------error'3021'

rst.Close
End Function
 
Hi Johnny,

Just to confirm:

On your form, you have all the textboxes needed,
All the textboxes' format property is set to 'Currency'
And all the records in rst exist

Really not too sure what to suggest. If you like, you could send me all you have by email and I'll take a proper look at it.

r.harrison891@gmail.com
 
vbaInet said:
Not Nz()?

Anyway, we're dealing with a recordset here so a RecordCount is what you're after.
Click to expand...

Perhaps reread Post #7

Test recordcount before you apply your format, if recordcount is 0 then pass back $0.00

JR
 
Can't believe I missed that post! That would have saved me a headache!
 
You must log in or register to reply here.

Similar threads

S
Solved Getting "No Current Record" on query that previously worked
Replies
6
Views
489
Gasman
Gasman
N
Solved How to convert a month into a number in MS Access Query
Replies
7
Views
502
Pat Hartman
P
medihub_guy
SQL Select Statement for Quantity On Hand
Replies
18
Views
1,449
Pat Hartman
P
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
2,073
Cotswold
Cotswold
P
Approaches to using saved queries for doing calculations on forms
2
Replies
32
Views
2,623
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom