Help With VB Code

[UTLee]

I have a report in Access that someone else built and I need to update it but have very limited experience with VB. Here's the code:

Public Function GetTotalUSEquity(strClient As String) As Double
    Dim rs As New ADODB.Recordset
    Dim dblTotalUSEquity As Double
    Set rs = cn.Execute("select sum(actamount) as TotalUSEquity from qryAccounts where Client = '" & strClient & "' and CategoryId = " & 302)
    Do While Not rs.EOF
        dblTotalUSEquity = rs("TotalUSEquity")
        rs.MoveNext
    Loop
    GetTotalUSEquity = dblTotalUSEquity
End Function

This works fine on the report but only shows the sum of values that are associated with categoryID 302.

I need this code to include the values of not only categoryID 302 but also 303, 304, and 305. Any idea how to add these categories into this code?



Thanks in advance.

[rpeare]

Set rs = cn.Execute("select sum(actamount) as TotalUSEquity from qryAccounts where Client = '" & strClient & "' and CategoryId = " & 302)

is only examining category 302

What you will likely have to do is alter this function so that you pass it both the strClient and the category unless you are summing the categories of 302 through 305 then you would need something more like:

Set rs = cn.Execute("select sum(actamount) as TotalUSEquity from qryAccounts where Client = '" & strClient & "' and (CategoryId between " & 302 & " AND " & 305 & ")" )

Barring that you'd have to put in the values individually (though I'm curious what 302 actually is if it's a field name on a form or if it's a hardcoded value)

[UTLee]

Perfect, that worked! Thanks for your help! I didn't know the syntax on how to specify between 302 and 305. Do you know if I can add to this 50% of categoryID 401?

[rpeare]

Set rs = cn.Execute("select sum(actamount) as TotalUSEquity from qryAccounts where Client = '" & strClient & "' and (CategoryId between " & 302 & " AND " & 305 & ")" )

if this is your formula now you'd have to modify the sum(actamt) formula to be something like

sum(iif(categoryid between 302 and 305, actamount, actamount * .5)) as TotalUSEquity

[UTLee]

OK, so how do I specify to add in the categoryID 401 and no other categoryIDs except for the range we created earlier of 302-305? There are many other categoryIDs that I don't want in this query (there are categoryIDs from 100 to 900).

[ranman256]

This should have been done as a query, NOT code.

[rpeare]

Personally I would set up a table that tells me which codes are valid for this particular function, I don't like that this function can do what you can do in a query either particularly as your expression gets more complex. That said your expression would be something like:

Set rs = cn.Execute("select sum(actamount) as TotalUSEquity from qryAccounts where Client = '" & strClient & "' and (CategoryId between " & 302 & " AND " & 305 & " OR CategoryID = 401)" )

[Xipooo]

Actually the "IN" statement would do the same thing and create a bit less confusion if you have to add more codes later.

Set rs = cn.Execute("select sum(actamount) as TotalUSEquity from qryAccounts where Client = '" & strClient & "' and CategoryId IN (302, 303, 304, 305)")

If you ever need to add more categories just add it to the list inside of the parenthesis.

[UTLee]

Yep, that worked. Thanks again. I know this report isn't particularly efficient but I don't really have any choice but to keep building on to it using what's already in place. I inherited the duty of making this thing work and have zero programming/access experience.

I appreciate the hand holding!