Multiplication in a query

[herbertioz]

I try to multiply all records in a field in Access. For example:
2*2*3*3=36

ID Number
1 2
2 2
3 3
4 3

How can I do that with a query? is it supported?

 

[pbaldy]

I can't think of a way to multiply like that. Sum would be simple, but for multiplying you'll probably have to create a custom function to do it. Open a recordset and loop the records, multiplying as you go.

 

[herbertioz]

Ok, thanks,Pbaldy! I am going to write the function and add it here.

 

[pbaldy]

No problem, post back if you get stuck on it.

 

[herbertioz]

No problem, post back if you get stuck on it.

Private Sub Command40_Click()
Dim rs As DAO.Recordset
Dim test As Double
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Numbers")

If Not (rs.EOF And rs.BOF) Then
    Do Until rs.EOF = True
         test = rs.Fields("Number")
       
        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
     Loop
    
Else
    MsgBox "There are no records in the recordset."
End If
MsgBox (test)

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub

I have tried to write some vba code and it write out the last number (last record of the field number). How can I get it to multiply each record when its looping down the records?

 

[pbaldy]

Declare a variable, and set it to 1 before the loop. Inside the loop:

VariableName = VariableName * rs.Fields("Number")

Then at the end, output the variable. I'd use Currency or Decimal, or at least be aware that Double is a floating point type and 2+2 may equal 4.0000000001 instead of 4.

 

[herbertioz]

Thanks! That worked!
The final working code:

Private Sub Command40_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Numbers")
Dim test As Integer
test = 1

If Not (rs.EOF And rs.BOF) Then
    Do Until rs.EOF = True
        
       test = test * rs.Fields("Number")
       
        rs.MoveNext
     Loop
    
Else
    MsgBox "There are no records in the recordset."
End If
MsgBox (test)

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub

 

[pbaldy]

Happy to help!