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?
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?
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Perhaps you could explain your requirement more fully. Sorry, but I have no understanding of your last post.
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Multiply data in one filed inside eachother
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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.
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):
- You'd substitute your query and field names with your proper ones.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
- 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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Tnx for that i will tray it early in the morning let u know if it works.
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
I also tryed on some simple access database, and if i filter query by form it returns Error 3061: Too few parameters. Expected 1.
https://easyupload.io/lsknc5 -> upload of simple db
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]