locked
Dlookup - Query parameter RRS feed

  • Question

  • Hi,

    Is it possible to use the Dlookup function on a query with a parameter ?

    Thanks.


    hrubesh
    Thursday, July 14, 2011 5:04 PM

Answers

  • No, it's not.

    Please explain what you're trying to do, and someone should be able to suggest an alternative approach.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by hrubesh Monday, August 1, 2011 7:42 PM
    Thursday, July 14, 2011 5:18 PM
  • hrubesh wrote:

    I will try insert them all in the record source, but I have a feeling it
    will still slow the query because:

    Anything will be better than DLookups. Just give it a try. ;-)


    Peter Doering [MVP Access]

    • Marked as answer by hrubesh Monday, August 1, 2011 7:42 PM
    Sunday, July 17, 2011 10:45 AM
  • Hello everyone,

    I had partially used the VBA code from the previous version of the program.

    Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
      SetPic
      
      Dim db As Database
      Dim qd As QueryDef
      Dim rs As Recordset
      
      Set db = CurrentDb()
      Set qd = db.CreateQueryDef("")
      qd.SQL = "SELECT * from qryremedy"
      On Error Resume Next
      qd.Parameters![@ind] = Right(Me.TxtStuIndex, 4)
      Set rs = qd.OpenRecordset()
      
      txtWeak = rs(3)
    
    End Sub
    

    I still prefered this version to maximise the use of SQL code. The moment I was guided to change my table design, normalise them and then introduced to use Transform and Pivot, I could see this whole project done via SQL. This was the temporary solution I decided on the spot. My colleague was besides me for the printing and One report was taking him about 50 seconds if I remember well at a certain point in time just to load. I looked at him and said wait (maybe in my mind) and quickly tried the VBA (The previous version was mostly VBA).

    I am currently trying to use joins as much as I can. Before, I have worked in an environment where nested simple SQL statements were prefered on SQL server. Slowly, the join is making sense now :)

    Many thanks to you and everyone else.

     

     


    hrubesh
    • Marked as answer by Macy Dong Tuesday, August 2, 2011 2:13 AM
    Monday, August 1, 2011 7:34 PM

All replies

  • No, it's not.

    Please explain what you're trying to do, and someone should be able to suggest an alternative approach.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by hrubesh Monday, August 1, 2011 7:42 PM
    Thursday, July 14, 2011 5:18 PM
  • I have this code in a textbox in a report:

    =DLookUp("Remedy","Qryremedy","student = " & [student])

    Now, that query takes a very long time and I decided to insert a parameter in it. (@student) I tried run the query and now it is very fast since it loads for only one student.

    But the Dlookup does not accept that.

    Is there any other direct function from the report?

    Thanks.


    hrubesh
    Thursday, July 14, 2011 5:32 PM
  • hrubesh wrote:

    I have this code in a textbox in a report:

    =DLookUp("Remedy","Qryremedy","student = " & [student]) [...]

    Is there any other direct function from the report?

    Include Qryremedy in the RecordSource of the report. If not every student
    has a Remedy, connect Qryremedy using Left Join. If you have problems with
    this description, post the RecordSource.


    Peter Doering [MVP Access]

    Thursday, July 14, 2011 6:16 PM
  • The report has 3 sections:

    A header : Student full name, class.

    A body: student marks (many subjects, many marks)

    A footer: Based on the marks , there are two remarks given. One called subject to remedy and the other called general remark.

    The information that the body contains is brought a query in the recordsource of the report. This query also generates what is called subject remark. Each record in this body has the index of the student that repeats.

    - Index - Subject - Mark - Subject remark

     

    Now the Header;
    I use the index which is group in the report, in Dlookup to obtain the name and class of the student.

     

    In the footer,

    I use the index in a DLookup to obtain his remedy and general remarks.

     

    I will try insert them all in the record source, but I have a feeling it will still slow the query because:

    remedy is: a list of subjects the student did not work well.

    General remarks counts the number of subject failed. It then compares this number to the subject failed of other students before choosing a subject remark in a list of many remarks.

     

    I have replaced the Dlookup with VBA. With DLOOKUP to bring remedy, the report takes 8 seconds when the student has 8 subjects (2 papers each) to load each page.

    For another academic class, when the report has 5-6 subjects ( 1- 4 paper each) the report takes 50 seconds to load each page. That became impossible. I replaced the DLookup with VBA. The report generated in 4 seconds.

     

    I am still sure, the same (4-8 seconds)  can be achieved without the use of VBA coding to open a dabatase.

    I will try the query with a left join and let you know in time. (Yes all students have a remedy - Good or for improvement)

     

    Thanks.

     

     


    hrubesh
    Thursday, July 14, 2011 7:23 PM
  • hrubesh wrote:

    I will try insert them all in the record source, but I have a feeling it
    will still slow the query because:

    Anything will be better than DLookups. Just give it a try. ;-)


    Peter Doering [MVP Access]

    • Marked as answer by hrubesh Monday, August 1, 2011 7:42 PM
    Sunday, July 17, 2011 10:45 AM
  • Hi Brubesh,

     

    Thank you for participating in our forum.

     

    What's the status after you trying your workaround?

    Could you please share the result and your experience with us?

     

    Best Regards,


    Macy Dong [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 22, 2011 9:34 AM
  • I need some more time before I try it.

    The part above was part of a solution to generate student reports. The reports were printed on the 14th and 15th of July.

    I am now completing the management reports of that batch. My time frame is limited until the Mgt Reports are done. I hope 10 days to create the reports (Analyse requirements, design queries and reports) and then print them.

    Thank you for your kind help.


    hrubesh
    Friday, July 22, 2011 1:01 PM
  • Hello everyone,

    I had partially used the VBA code from the previous version of the program.

    Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
      SetPic
      
      Dim db As Database
      Dim qd As QueryDef
      Dim rs As Recordset
      
      Set db = CurrentDb()
      Set qd = db.CreateQueryDef("")
      qd.SQL = "SELECT * from qryremedy"
      On Error Resume Next
      qd.Parameters![@ind] = Right(Me.TxtStuIndex, 4)
      Set rs = qd.OpenRecordset()
      
      txtWeak = rs(3)
    
    End Sub
    

    I still prefered this version to maximise the use of SQL code. The moment I was guided to change my table design, normalise them and then introduced to use Transform and Pivot, I could see this whole project done via SQL. This was the temporary solution I decided on the spot. My colleague was besides me for the printing and One report was taking him about 50 seconds if I remember well at a certain point in time just to load. I looked at him and said wait (maybe in my mind) and quickly tried the VBA (The previous version was mostly VBA).

    I am currently trying to use joins as much as I can. Before, I have worked in an environment where nested simple SQL statements were prefered on SQL server. Slowly, the join is making sense now :)

    Many thanks to you and everyone else.

     

     


    hrubesh
    • Marked as answer by Macy Dong Tuesday, August 2, 2011 2:13 AM
    Monday, August 1, 2011 7:34 PM
  • Hi Hrubesh,

     

    Thank you for your response and sharing the workaround with us.

     

    Excellent!

     

    SQL is interesting, right? :)

     

    Best Regards,


    Macy Dong [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 2, 2011 2:10 AM
  • My pleasure,

    I love SQL.

    Practice makes perfect.

    This is all left to do now :)

    Thanks.


    hrubesh
    Tuesday, August 2, 2011 6:24 AM