# How to multiply values on report same row

Windows 10 Access 2016
Join Date
May 2020
Posts
31

## How to multiply values on report same row

How can in multiply values showed on report by same row in query.
I need something like if i set =sum(table row name) su does not matter how many record are showed on report all of them will be sum-ed.
I need same but not to sum it to multiply it?

2. Originally Posted by inke01
How can in multiply values showed on report by same row in query.
Create a calculated field in the query. Something like:

Total: [FieldName1]*[FieldName2]

Windows 10 Access 2016
Join Date
May 2020
Posts
31
Cant couse resolt are in same filed.

4. Perhaps you could explain your requirement more fully. Sorry, but I have no understanding of your last post.

Windows 10 Access 2016
Join Date
May 2020
Posts
31
Easy way to explain:

How to multiply all values from somatic cell field.
something like =sum(somatic_cells)
instead of sum tu use something to multiply it.

6. Originally Posted by inke01
Easy way to explain:

How to multiply all values from somatic cell field.
something like =sum(somatic_cells)
instead of sum tu use something to multiply it.
So what would be the result of the calculation with the given data

Windows 10 Access 2016
Join Date
May 2020
Posts
31
1000*5000*7000*8000

Dont know how tu multiply it in field and set it to report.

Windows 10 Access 2016
Join Date
May 2020
Posts
31
Multiply data in one filed inside eachother

9. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
7,335
I think you'll have to either create a crosstab query to get the data into rows and then calculate as you were shown, or write a function. I suspect the issue with the crosstab would be that you might have too many rows of data - I forget what the ct field limit is.

A function would have to loop over a recordset. I would probably create a variable before the loop and assign it the value of 1. Then the loop would multiply the variable by each field value that isn't null, and do the division on the result at the end. This way, it would not matter how many rows were involved, which might be another reason why a crosstab likely isn't suitable as the number of fields will likely vary from time to time and as I mentioned, you probably would have too many fields anyway.

Windows 10 Access 2016
Join Date
May 2020
Posts
31
oky. if i can get help to write funcion.
So it is like this all records in query are filterd to show only those for last 3 months it will be 1 record pro month for somatic cells. Like this ">DateAdd("m";-3;Now())" . Now when i put them to report as subreport to show it for each costumer his somatic cells have to multipy the values from last 3 months (3 records). Is therre any easyest way to multiply it without funcion or i have to write it. "Still not enough good with vb" learning it.

11. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
7,335
I have no idea if you can multiply values in successive rows in a report of any kind. You can do running sums with report design, but as far as I know, not multiplication. You could set the recordsource of a form or report control to the value that a function returns, but this control would have to be in a header or footer I think. Something like this (untested):
Code:
```Function CalculateSomatic() As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngProduct As Long

On Error GoTo errHandler
lngProduct = 1
Set db = CurrentDb
Set rs = db.OpenRecordset("yourQueryName", dbOpenDynaset)

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do While Not rs.EOF
lngProduct = lngProduct * rs.Fields("yourQueryFieldNameHere")
Move Next
Loop
End If

exitHere:
CalculateSomatic = lngProduct
Set db = Nothing
Set rs = Nothing
Exit Function

errHandler:
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Function```
- You'd substitute your query and field names with your proper ones.
- The function has to go in a standard module (i.e. not in a form or report module).
- Your control recordsource would be = CalculateSomatic()
If your values become too large for long data type, change Long to Single or Double.

Windows 10 Access 2016
Join Date
May 2020
Posts
31
Tnx for that i will tray it early in the morning let u know if it works.

Windows 10 Access 2016
Join Date
May 2020
Posts
31
Ok. I just tried couple ways to put funcion in standar module and call it on report - > Recieved Error 3061: Too few parameters. Expected 3. (it is connected to query witch filters somatic cels by user and type of milk for past 3 months).
If i connect it to query witch contain all data for last 3 months (not filterd by user and type of milk) i calculates all and retuns me Error 6: overflow
I tryed to put funcion in report header or footer and same thin happend.
Also i funcnction i changed
Code:
```Function CalculateSomatic() As Long TO Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngProduct As Long To Double

On Error GoTo errHandler
lngProduct = 1
Set db = CurrentDb
Set rs = db.OpenRecordset("query_somatic_cells", dbOpenDynaset)

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do While Not rs.EOF
lngProduct = lngProduct * rs.Fields("somatic_cells")
Move Next rs.MoveNext
Loop
End If

exitHere:
CalculateSomatic = lngProduct
Set db = Nothing
Set rs = Nothing
Exit Function

errHandler:
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Function```

Windows 10 Access 2016
Join Date
May 2020
Posts
31
I also tryed on some simple access database, and if i filter query by form it returns Error 3061: Too few parameters. Expected 1.

Windows 10 Access 2016
Join Date
May 2020
Posts
31
I was searching forum whole day and internet should i implement something like
Set rs = qdf.OpenRecordset()

With rs
[Forms]![finn_namn]![Namn] = !Name
End With

Couse my query is set to filter by Form : One by buyers [Forms]![frm_m_ispis_izvjesca_kooperanti]![Combo8]
and date Between [Forms]![frm_m_ispis_izvjesca_kooperanti]![datefrom] And [Forms]![frm_m_ispis_izvjesca_kooperanti]![dateto]

Page 1 of 4 1234 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums