Adding a Field value as variable (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 11:07
Joined
Sep 15, 2012
Messages
229
OK so just learning here... getting past a few early hurdles will really help. Easy one for you pros!

In Table [QueryTest] there are 20 rows of data. I want to add each of the 20 values in field [MarketValueImpact] together to get one cumulative value, using a loop.

The code breaks at the bold line, specifically where the ???'s are. If I use a number there it works, if I use a field name or variable it does not.

Any help is appreciated
____________________________________
Public Sub OpenRecordset()

Dim i As Integer
Dim ropen As Integer
Dim impact As Integer

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("QueryTest")
ropen = rs("MarketValImpact")
impact = rs("MarketValImpact")

For i = 0 To rs.RecordCount - 1
impact = rs("MarketValImpact")
ropen = ropen + impact
Debug.Print (ropen)
rs.MoveNext
Next i

rs.Close
Set rs = Nothing
db.Close

End Sub
 

Tskutnik

Registered User.
Local time
Today, 11:07
Joined
Sep 15, 2012
Messages
229
Thanks for the quick note back. Sorry for my shortcut post...
When I get the variables figured out the values will be multiplied together, not added. I was adding to start, just to learn how this all worked.

If there is a function that grabs a group of values in one field and multiplies them all together (like XL's Product) that will work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
21,358
Thanks for the quick note back. Sorry for my shortcut post...
When I get the variables figured out the values will be multiplied together, not added. I was adding to start, just to learn how this all worked.

If there is a function that grabs a group of values in one field and multiplies them all together (like XL's Product) that will work.
Hi. I'm still away from my computer, but have you tried looking at my DDiff() function and simply change it from a minus to a multiply?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:07
Joined
May 21, 2018
Messages
8,463
If there is a function that grabs a group of values in one field and multiplies them all together (like XL's Product) that will work.
There is now
Code:
Public Function Dproduct(Expr As String, Domain As String, Optional Criteria As String = "") As Double
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "SELECT " & Expr & " FROM " & Domain
  If Criteria <> "" Then
    strSql = strSql & " WHERE " & Criteria
  End If
  Set rs = CurrentDb.OpenRecordset(strSql)
  If rs.EOF Then Exit Function
  Dproduct = 1
  Do While Not rs.EOF
    Dproduct = Dproduct * rs.Fields(Expr)
    rs.MoveNext
  Loop
 
End Function
 

Tskutnik

Registered User.
Local time
Today, 11:07
Joined
Sep 15, 2012
Messages
229
Thanks Maj - can I ask a favor - I want to make sure I don't stumble around for 4 hours trying to translate this. Once I can see it done correctly it will help me with other code I need to write.
Here is specifically what I need:
As example - see the attached spreadsheet

Table = Query Test
1) For every record where the concatenation of fields [AcctKey] & [SecKey] match, multiply the values in field [UnitizedVal].
2) There are different combinations of [AcctKey] & [SecKey] so you have to loop through the whole table.

For a table like this the code would produce 4 results
100 ABC123 = 1.0612602
100 DEF123 = .09380072
200 ABC123 = 0.9702868
300 ABC123 = 1.03140492

I REALLY appreciate your help. Im stuck.
 

Attachments

  • QueryTest.zip
    10.2 KB · Views: 98

Tskutnik

Registered User.
Local time
Today, 11:07
Joined
Sep 15, 2012
Messages
229
Hi. I'm still away from my computer, but have you tried looking at my DDiff() function and simply change it from a minus to a multiply?
Sorry I could not translate it to what I need.

This sort of started with what felt like a simple problem with defining and adding a variable. I really appreciate everyone's time.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
21,358
Sorry I could not translate it to what I need.

This sort of started with what felt like a simple problem with defining and adding a variable. I really appreciate everyone's time.
Hi. No worries. Looks like @MajP already did it for you. Cheers!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:07
Joined
May 21, 2018
Messages
8,463
Here is a wrapper for the original function.
Code:
Public Function Dproduct(Expr As String, Domain As String, Optional Criteria As String = "") As Double
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "SELECT " & Expr & " FROM " & Domain
  If Criteria <> "" Then
    strSql = strSql & " WHERE " & Criteria
  End If
  Set rs = CurrentDb.OpenRecordset(strSql)
  If rs.EOF Then Exit Function
  Dproduct = 1
  Do While Not rs.EOF
    Dproduct = Dproduct * rs.Fields(Expr)
    rs.MoveNext
  Loop
End Function

Public Function GetAcctProd(AcctKey As Variant, secKey As Variant) As Double
  Const QueryName = "tblData"
  Const FieldName = "UnitizedVal"
  Dim strWhere As String
  'If these keys are numeric you need to remove the single quotes
  strWhere = "AcctKey = '" & AcctKey & "' AND SecKey = '" & secKey & "'"
  GetAcctProd = Dproduct(FieldName, QueryName, strWhere)
End Function
Use that in a query like
Code:
SELECT
TblData.AcctKey,
TblData.SecKey,
GetAcctProd([acctkey],[SecKey]) AS UnitizedProduct
FROM TblData
GROUP BY
TblData.AcctKey,
TblData.SecKey;


qryTest

AcctKeySecKeyUnitizedProduct
100ABC123
1.0612602​
100DEF123
0.9380072​
200ABC123
0.9702868​
300ABC123
1.03140492​


Look at the the wrapper function and change names to meet your names. Remove single quotes if this fields are numeric.
 

Attachments

  • MajP_QueryTest.zip
    24.2 KB · Views: 90

Users who are viewing this thread

Top Bottom