Page 1 of 4 1234 LastLast
Results 1 to 15 of 54
  1. #1
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32

    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. #2
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,765
    Quote Originally Posted by inke01 View Post
    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]
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    Cant couse resolt are in same filed.



    Click image for larger version. 

Name:	Example.png 
Views:	72 
Size:	52.9 KB 
ID:	42552

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,765
    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

  5. #5
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    Easy way to explain:

    Click image for larger version. 

Name:	Example 1.png 
Views:	71 
Size:	1.2 KB 
ID:	42553

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

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,765
    Quote Originally Posted by inke01 View Post
    Easy way to explain:

    Click image for larger version. 

Name:	Example 1.png 
Views:	71 
Size:	1.2 KB 
ID:	42553

    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
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    1000*5000*7000*8000

    Click image for larger version. 

Name:	Example 2.png 
Views:	69 
Size:	4.5 KB 
ID:	42554

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

  8. #8
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    Multiply data in one filed inside eachother

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,670
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  10. #10
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    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. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,670
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  12. #12
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    Tnx for that i will tray it early in the morning let u know if it works.

  13. #13
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    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

  14. #14
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    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

  15. #15
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    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 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-18-2019, 02:57 AM
  2. how to place a multiply in a query
    By darwin in forum Queries
    Replies: 6
    Last Post: 05-14-2015, 03:56 PM
  3. Opening multiply Forms?
    By djclntn in forum Forms
    Replies: 10
    Last Post: 04-02-2013, 04:20 PM
  4. Ado update for multiply records
    By thanosgr in forum Access
    Replies: 26
    Last Post: 03-16-2012, 01:26 PM
  5. Multiply front end on same computer?
    By efk0107 in forum Access
    Replies: 5
    Last Post: 01-25-2011, 08:39 AM

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