Loop Thru Table and ADD field values?

[WineSnob]

I have a table called [tblIncomeTemp] with fields [Yr](1,2,3,.....) and [Desired Income] and [ProposalID].
I am TRYING to loop through the table and add up the [Desired Income] for a specified number of Years. Not sure how to do that though. Below is what I am trying to use. Something is wrong though!
Thanks in advance for any help.

Function GetDesiredIncomeTotal(nProposalID As Long, nYears As Integer) As Currency
Dim cDesiredIncomeTotal As Currency
Dim rs As Recordset
Dim nYear As Integer

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblIncomeTemp] WHERE [ProposalID] = " & nProposalID)


While Not rs.EOF

For nYear = 1 To nYears
Debug.Print nYear
If nYear = 1 Then
cDesiredIncomeTotal = rs![Desired Income]
Else
cDesiredIncomeTotal = rs![Desired Income] + rs![Desired Income]
End If

Next nYear

rs.MoveNext
Wend

GetDesiredIncomeTotal = cDesiredIncomeTotal

Debug.Print GetDesiredIncomeTotal

End Function





Function TestGetDesiredIncCalc()
GetDesiredIncomeTotal 1003, 5
End Function

 

[PRMiller]

What is going wrong? Are you receiving an error message?

 

[WineSnob]

No I am getting the Last [Desired Income] Value in the table. I assume it is the .EOF.
I only want to add the [Desired Income] for Yr 1 to nYears (5)
Here are the values in the table.
Yr Desired Income
1 5000
2 5050
3 5100.50
4 5151.51
5 5203.02
6 5255.05

Therefore if I am sending 5 as nYears then I want to add
5000+5050+5100.50+5151.51+5203.02 = RESULT 25505.03

if I am sending 6 as nYears then I want to add
5000+5050+5100.50+5151.51+5203.02+5255.05 = RESULT 30760.08

 

[Beetle]

Don't think you want the .EOF here, you just want to start at the first record and loop from 1 to nYears. You might want to add an Order By clause just to make sure the recordset is always sorted correctly. Also, instead of adding the current field value to itself, you need to add the current field value to the current value of the variable. Try this;

Function GetDesiredIncomeTotal(nProposalID As Long, nYears As Integer) As Currency
Dim cDesiredIncomeTotal As Currency
Dim rs As Recordset
Dim nYear As Integer

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblIncomeTemp] WHERE [ProposalID] = " & nProposalID)

cDesiredIncomeTotal = 0

With rs
    .MoveFirst
    For nYear = 1 To nYears
        cDesiredIncomeTotal = cDesiredIncomeTotal + ![Desired Income]
        .MoveNext
    Next nYear
End With

GetDesiredIncomeTotal = cDesiredIncomeTotal

Debug.Print GetDesiredIncomeTotal

End Function

 

[WineSnob]

Perfect! Thanks

 

[WineSnob]

Beetle's code worked fine UNTIL I added a start year and end year as arguments. Now I want to sum in blocks of years 1-5 or 4-6 or 6-11. Here is the modified code.
Now I think it is the .MoveFirst giving me the problem.

How do I use the nStartYear and nEndYear as the range?

Function GetDesiredIncomeTotal(nProposalID As Long, nStartYear As Integer, nEndYear As Integer) As Currency
Dim cDesiredIncomeTotal As Currency
Dim rs As Recordset
Dim nYear As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblIncomeTemp] WHERE [ProposalID] = " & nProposalID)
cDesiredIncomeTotal = 0
With rs
.MoveFirst
For nYear = nStartYear To nEndYear
cDesiredIncomeTotal = cDesiredIncomeTotal + (![Desired Income] * 12)
Debug.Print "nYear: " & nYear & " Amt: " & cDesiredIncomeTotal
.MoveNext
Next nYear
End With
GetDesiredIncomeTotal = cDesiredIncomeTotal
Debug.Print GetDesiredIncomeTotal
End Function

 

[Beetle]

In that case you could just use nStartYear and nEndYear as additional criteria in your query, so it only returns the records you'll be working with. Then, you could just loop from the first record to .EOF, you wouldn't really need -

For nYear = nStartYear To nEndYear

unless you want that for some other purpose. Might not make much difference either way. I've left it in for this example;

Function GetDesiredIncomeTotal(nProposalID As Long, nStartYear As Integer, nEndYear As Integer) As Currency

Dim cDesiredIncomeTotal As Currency
Dim rs As dao.Recordset
Dim nYear As Integer
Dim strSQL As String

strSQL = "SELECT * FROM [tblIncomeTemp] WHERE [Proposal ID] = " & nProposalID _
       & " And [Yr] Between " & nStartYear & " And " & nEndYear _
       & " Order By tblIncomeTemp.Yr;"
       
Set rs = CurrentDb.OpenRecordset(strSQL)

cDesiredIncomeTotal = 0

With rs
    .MoveFirst
    For nYear = nStartYear To nEndYear
        cDesiredIncomeTotal = cDesiredIncomeTotal + (![Desired Income] * 12)
        Debug.Print "nYear: " & nYear & " Amt: " & cDesiredIncomeTotal
        .MoveNext
    Next nYear
End With

GetDesiredIncomeTotal = cDesiredIncomeTotal

Debug.Print GetDesiredIncomeTotal

End Function

 

[WineSnob]

I get an error "Too few parameters. Expected 1."

 

[Beetle]

If you just did a copy/paste of the example code than make sure the field names are all correct in the SQL statement (spelling/spaces).

 

[WineSnob]

I did copy/paste.
All the field names look correct. I really struggle with SQL statements and the " and & and " syntax. I tried to deleting the ; at the end after the Order By tblIncomeTemp.Yr;" No luck.
Do I need the replace the
With rs
.MoveFirst

to read
While not rs.EOF


Wend

This rs stuff is new to me so Thanks for your patience!

 

[Beetle]

Can you post a copy of your app with just some bogus data?

 

[WineSnob]

Here it is all stripped down. Just the Table and the Module.
I am using
Function TestGetDesiredIncCalc()
GetDesiredIncomeTotal 1001, 6, 10
End Function
to test the function.

Actually the value of the Desired Income is a monthly amount and must be multipled by 12 to return an Annual Desired Income. You can see where I am trying ![Desired Income]*12

In the above test function it should return.
$69,556.44
$71,643.14
$73,792.43
$76,006.20
$78,286.39
$369,284.61

Thanks Again

I can't add an attachment. The Browse doesn't work for me to select the db. Will trying to upload seperately.

 

[WineSnob]

I guess it is a Windows 7 thing or my new computer. Here it is from my old XP.
Thanks Much

 

[Beetle]

In the SQL statement in your procedure, the [Proposal ID] field has a space in it. In the table that field has no space in the name. After correction, here is the results from the immediate window when running the function with the test parameters you have in there;


?TestGetDesiredIncCalc
nYear: 1 Amt: 60000
nYear: 2 Amt: 121800
nYear: 3 Amt: 185454
nYear: 4 Amt: 251017.62
nYear: 5 Amt: 318548.1492
nYear: 6 Amt: 388104.594
388104.594

I didn't verify the results, but the function runs.

 

[WineSnob]

Thanks AGAIN!.
I guess I looked at too long and could not see the obvious.