Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 54
  1. #31
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822

    Review https://www.accessforums.net/showthread.php?t=81401
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #32
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    I tryed that code yesterday and it works fine now im trying to implement it in my db and first i try it on simple one and get error for:

    Without this it works fine filters field where is no entery and still calculates left values.
    But when i add this AND ((test.datum)>DateAdd("m",-3,Now())) i recieve error.

    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 & ") AND ((test.usporedba)<>0) AND ((test.datum)>DateAdd("m",-3,Now())));"  error is on "m" Expect end of statement.
    
    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

  3. #33
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Use apostrophes.

    'm'
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #34
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    I got all that to work. But the problem seems to be more than complicated.
    Let me explain the db.
    I have query with sql:
    Code:
    SELECT tbl_m_kupci_kooperanti.kooperanti, tbl_m_rezultati.somatske_stanice, tbl_m_prijem.datum_prijema, tbl_m_prijem.Kupac
    FROM tbl_m_prijem RIGHT JOIN ((tbl_m_kupci_kooperanti RIGHT JOIN tbl_m_obrada ON tbl_m_kupci_kooperanti.[idkupci_kooperanti] = tbl_m_obrada.[idkupci_kooperanti]) RIGHT JOIN tbl_m_rezultati ON tbl_m_obrada.[idobrada] = tbl_m_rezultati.[idobrada]) ON tbl_m_prijem.id_m_prijem = tbl_m_kupci_kooperanti.id_m_prijem
    WHERE (((tbl_m_rezultati.somatske_stanice)<>0) And ((tbl_m_prijem.datum_prijema)>DateAdd("m",-3,Now())) And ((tbl_m_prijem.Kupac)=Forms!frm_somatske_stanice!combo_kupac));
    And the function will multipy all data from there but what i really need is when i open report it groups data by Buyer -> Cobuyer _>type of milk -> Somaticcells.
    Here is report oultook:
    report_somatske.pdf

    So what i really need is when data sorted in report than to be multiply. Something like sum what does but only to multiply????
    Click image for larger version. 

Name:	raw report.png 
Views:	44 
Size:	9.6 KB 
ID:	42632

    Click image for larger version. 

Name:	Somatic.jpg 
Views:	45 
Size:	81.0 KB 
ID:	42631

  5. #35
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So now you want the calculation to be based on a grouping in a report? I have no idea if that's possible. Individual calculations are possible when doing multiplication, but can they be displayed on a report? Would be simple if the calculation was a count or a sum, but multiplication?? Will have to give it some thought.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #36
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Handling very large numbers in Access is difficult. Review

    https://support.microsoft.com/en-us/...refre%20...%20

    https://stackoverflow.com/questions/...-number-in-vba

    Expect will need VBA custom function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #37
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good point. The values might need to be expressed as thousands or millions. The sample data was no where near as large. This is one of those threads where the critical information changes at every step. The puzzle is still how to multiply rows of data over a group and show each group aggregate result in a report.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #38
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

  9. #39
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    It was not my mind. I need to resolve this and was not reading all. So after your warning i droped all content on that form. And will it not repeat again. Sry about that. Can we still consider your help about this problem or?
    Tnx in advance.

  10. #40
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    Anyone, Micron i'm waiting for your answer if u can help??? I'm trying all day to bring prolem down.
    I make it possibile but it is to hard have to do single print for every cobuyer.
    Best way will be if it can be done on Report.

  11. #41
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Cross posting is allowed - you just need to do it properly as per the link I provided in the other forum. What bugs me is that you involved me and several other members here in a lengthy dialog and then just turned your back and went elsewhere. If you were not happy with our efforts you should have had the decency to politely say so.

    As for your problem, I have not thought of an answer but will check it out further if you post a db with the latest changes. Or you can consider an idea that popped into my head, which is to group your data in Excel, perform the calculations in some fashion and then link the spreadsheet in Access as you would a table. You could then probably get the calculated value - but how I don't know; pivot table? Can you link a pivot table spreadsheet to Access? Don't know but I'm not willing to try because my Excel pivot table knowledge isn't good enough.

    As for your other thread, maybe you should go back and restore it and you might get an answer from someone who can figure this out. Just post a link back to here. If you Google this problem, you will find a few who have asked, but from what I saw, no one had a solution. Your request is quite complicated; at least until someone figures it out.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #42
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    I 'm happy couse u are helping here. Tihs is great that forums and people exist who are ready to help.

    On other forum i cant post url it's reporting me that is spam i tryed with insert link, and url /url with url-s are in [].
    I receve this error:
    Oops! We ran into some problems.
    • Your content can not be submitted. This is likely because your content is spam-like or contains inappropriate elements. Please change your content or try again later. If you still have problems, please contact an administrator.




    This is link on other forum:
    https://www.access-programmers.co.uk/forums/threads/multiply-records-on-report.313258/

  13. #43
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems they have a 10 post requirement to be able to post links. About all you can do in such cases is just post the name of a forum and post title, I guess.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #44
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Perhaps you missed my comment in my last post. If you want me to try anything else, post your updated db in this forum.

    I tried to send you a PM but you apparently chose to not receive them. I wouldn't think it's because your post count is too low.

  15. #45
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    32
    I was checking and it is turned on. I can recieve messages. I made all enterys in my db. I will post version with no enterys???

Page 3 of 4 FirstFirst 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