Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 53
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,327
    What is the data type of the bound column of your combo? I presume the date values are date data type and not string?
    EDIT: It would probably be easier to move the sql of the query into the function - or is that query sql really long? Maybe post it if it's not.
    Otherwise I suspect you'll have to declare and set Parameters, or you could try to add parameters to the query property sheet.



    Bold = things you could Google if you want to try yourself or to research the concepts.
    - "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

  2. #17
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    31
    This is the form witch opens query and report, it filters by date from, date to and by costumer name
    Click image for larger version. 

Name:	Form.png 
Views:	24 
Size:	15.4 KB 
ID:	42612

    And this is sql code from that query:

    SELECT tbl_glavni_prijem.idprijem, tbl_m_prijem.datum_prijema, tbl_m_prijem.Kupac, tbl_glavni_prijem.datum_i_vrijeme_prijema, tbl_m_prijem.id_m_prijem, tbl_m_kupci_kooperanti.kooperanti, tbl_m_obrada.uzorci, tbl_m_prijem.[Datum ispitivanja], tbl_m_rezultati.mlijecna_mast, tbl_m_rezultati.proteini, tbl_m_rezultati.laktoza, tbl_m_rezultati.suha_tvar, tbl_m_rezultati.bezmasna_suha_tvar, tbl_m_rezultati.tocka_ledista, tbl_m_rezultati.broj_bakterija, tbl_m_obrada.[Datum uzorkovanja], tbl_m_obrada.[Broj zapisnika], tbl_m_rezultati.somatske_stanice
    FROM (((tbl_glavni_prijem RIGHT JOIN tbl_m_prijem ON tbl_glavni_prijem.[idprijem] = tbl_m_prijem.[idglavni_prijem]) RIGHT JOIN tbl_m_kupci_kooperanti ON tbl_m_prijem.[id_m_prijem] = tbl_m_kupci_kooperanti.[id_m_prijem]) 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]
    WHERE (((tbl_m_prijem.datum_prijema) Between [Forms]![frm_m_ispis_izvjesca_kooperanti]![datumod] And [Forms]![frm_m_ispis_izvjesca_kooperanti]![datumdo]) AND ((tbl_m_prijem.Kupac)=[Forms]![frm_m_ispis_izvjesca_kooperanti]![Combo8]));

  3. #18
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    31
    I created some simple db with only one table one query one form and one report
    And if i set on form 3 fields and one button two fields are date picker and one is costumer
    after applying that fields to be filter on query ect. [Forms]![frm_test]![combo_kupac]
    i recieve error 3061 to fiew parameters. Expected 3
    after i remove one filter from query
    same error with expected 2
    so how many fiters there are so many expected errors

    is there any possibility to filter it in a different way???

  4. #19
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,689
    Quote Originally Posted by inke01 View Post
    I created some simple db with only one table one query one form and one report
    And if i set on form 3 fields and one button two fields are date picker and one is costumer
    after applying that fields to be filter on query ect. [Forms]![frm_test]![combo_kupac]
    i recieve error 3061 to fiew parameters. Expected 3
    after i remove one filter from query
    same error with expected 2
    so how many fiters there are so many expected errors

    is there any possibility to filter it in a different way???
    Can you post a copy of the db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #20
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    31
    Link to download:

    https://easyupload.io/n11flm

  6. #21
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,689
    Quote Originally Posted by inke01 View Post
    When I said, "Can you post a copy of the db", I should have said, "Can you post a copy of the db on this forum​."
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #22
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,689
    If you ned help on posting a file here see: https://www.accessforums.net/faq.php...b3_attachments
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #23
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    31
    How tu upload it here. ?
    Can upload only 500Kb

  9. #24
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,689
    Quote Originally Posted by inke01 View Post
    How tu upload it here. ?
    Can upload only 500Kb
    Always pays to "Compact and Repair" and then zip it. Upload the zipped file.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #25
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    31
    here we go
    1.zip

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,327
    Don't have time to download and browse the posted db but this might help.
    What often works (and is simpler IMO) is to make variables equal to the values of the form controls and substitute your form control references in the query with those variables. Of course, you have to concatenate them. So if you declared dteDateFrom As Date, dteDateTo As Date, lngContractor As Long and concatenated the variables you'd have
    ... Between #" & dteDateFrom & "# And #" & dteDateTo "# AND ((tbl_m_prijem.Kupac)= " & lngContractor & "));"

    If that approach didn't work but was concatenated correctly, then there are other ways to get the parameters into the query or sql. I included the date delimiters (#) but confess I'm not absolutely sure that if declaring the variable as date data type that they are still needed.
    - "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. #27
    Bob Fitz's Avatar
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,689
    Quote Originally Posted by inke01 View Post
    here we go
    1.zip
    I think the attached db deals with the problem of criteria in the query.

    What is the purpose of the function called "CalculateSomatic "which is causing an error.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,327
    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.
    Last edited by Micron; 08-10-2020 at 07:01 PM.
    - "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

  14. #29
    inke01 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    31
    Micron tnx for that i just tried it works for this simple db i'll try tommorow to implement it to my db. And let u know how it works.
    PS. Any good book or something to learn more about access and vba???

  15. #30
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,327
    How to stop the function from running for every record:
    Code:
    Private Sub Report_Load()
    Me.Text11 = CalculateSomatic
    End Sub
    That should work for report view, print preview, or if the control is located in the report header or footer.
    - "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

Page 2 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 - Senior Forums