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.
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
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.
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:
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.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));
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????
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.
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.
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.
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.
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.
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.
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/
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.
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.
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???