Not sure how that query modification is supposed to solve the issue of how to multiply values of one field.
@inke01 - I told you to put the function in a standard module - you put it in the report module. Since you didn't say the query was based on form fields, you need to solve the parameter issue as well. Here is one way - declare a variable for the combo and use that in the sql in vba and not the query:
Code:
Function CalculateSomatic() As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngProduct As Long, lngKupac As Long
Dim strSql As String
On Error GoTo errHandler
lngProduct = 1
lngKupac = Forms!frm_test.combo_kupac
strSql = "SELECT test.ID, test.somatske_stanice, test.kupac, test.datum_prijema FROM test WHERE (((test.kupac)= " & lngKupac & "));"
Set db = CurrentDb
'Set rs = db.OpenRecordset("query_test", dbOpenDynaset)
Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do While Not rs.EOF
lngProduct = lngProduct * rs.Fields("somatske_stanice")
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
NOTE - I guess I forgot that the report would format itself 1x for every record, which unfortunately calls the function 1x per record. This may slow down the report opening to a point where it becomes too long. If that happens, another approach may be needed. However, it does work in your posted db version.
EDIT- first line came out the opposite of what I wanted to write. Corrected it.