locked
Dlookup (data from query) RRS feed

  • Question

  • Dear Experts,

    Below Lookup i place it at MS. Access Report Part (TextBox in  Control Source)

    =DLookUp("TotalLeaveTaken","qryLeaveTakenWithDate_Report1","[AnnualLeaveTypeID]=3" And "[EmployeeID]=qryLeaveTakenYTDWorker_Report.EmployeeID")

    I have tried the above Dlookup, it is works for partial only, and the bolds part i think it is something wrong and it can't works fine.
    Kindly advise is it above lookup (bold part) is wrong and what is the correct way ?

    Thanks.

    Danny

    Saturday, July 23, 2011 4:52 AM

All replies

  • Hi Danny

    Danny Gan wrote:

    =DLookUp("TotalLeaveTaken","qryLeaveTakenWithDate_Report1","[AnnualLeaveTypeID]=3"*And
    "[EmployeeID]=qryLeaveTakenYTDWorker_Report.EmployeeID"*)

    I have tried the above Dlookup, it is works for partial only, and the
    bolds part i think it is something wrong and it can't works fine.
    Kindly advise is it above lookup (bold part) is wrong and what is the
    correct way ?

    Not sure why the two queries have different names. Are both loaded? Or did you type the queryname in the AND part wrong?

    I'd expect something like this

    =DLookUp("TotalLeaveTaken", _
            "qryLeaveTakenWithDate_Report1", _
            "[AnnualLeaveTypeID]=3  And [EmployeeID]=" & _
            me!EmployeeID)

    assuming the DLookup is done in a report or form that has the employeeID in it.

    HTH
    Henry

    HTH
    henry

    Monday, July 25, 2011 7:08 AM
  • Dear Henry,

    Thanks for your reply.

    Actually is loaded one query in report. can report load 2 queries? Because this report is quite complicated due to have a sub report.

    It is  leave report. When user enter a range of date all type of leave will show out, sub report will show details transactions, day by day leave taken and main report will show the Summary (Total) days of leave taken (problem is happen here for above query).

    Thanks.

    Danny

    Monday, July 25, 2011 9:18 AM
  • Hi Danny

    Where is the field in which you want to display the result of the DLookup()? Is it in the Subreport? If not, you will not be able to access the data. And you can't refer to the query content, you have to refer to the report (in your case the subreport). You can't refer to the query. But with Me!yourSubReport.Report!EmployeeID you should be able to refer to the content of the query bound to your subreport.

    Henry

    Monday, July 25, 2011 10:06 AM
  • Hi Henry,

    Thanks for your reply. Is it posible i email you my VBA Access Report? It's quite difficult to explain here. Thanks.

    Danny

    Wednesday, July 27, 2011 12:57 AM
  • Hi Danny

    Danny Gan wrote:

    Thanks for your reply. Is it posible i email you my VBA Access Report?
    It's quite difficult to explain here. Thanks.

    No, doesn't make sense. Sorry

    The idea behind the forum is to find the answer to the questions here and not by e-mail.

    First you should give some more information about the Acces Version you are using and about the environment it's running in. Then maybe somebody else will have the time to look at your application onhand.

    Henry

    Wednesday, July 27, 2011 5:46 AM