Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup (Access 2000)

    In my Where clause in the report i want to use a DLookup function to insert a number from the table Affiliates.
    The table Affiliates looks like this:

    afid StockCustomer
    1 1008
    2 118
    3 119


    When i try a DLookup function, it inserts of course the first figure, i.e. 1008
    WHERE orders.customerid =DLookUp('StockCustomer','affiliates')"
    I open the report with the help of an option box of a form.The option box is called office, the form is called FBenchmark
    I want to try the following but i fail:

    WHERE orders.customerid =DLookUp('StockCustomer','affiliates',afid = Forms!FBenchmark!office)"
    I fail, even though the report is opened but blank.

    Where is my mistake?
    In short, if i press option 2 of the form, i want to insert the number 118

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup (Access 2000)

    The third argument of DLookup must be a string, like the other two. So try

    ... WHERE orders.customerid =DLookUp('StockCustomer','affiliates','afid = Forms!FBenchmark!office')"

    You could also add the affiliates table to the query and avoid the use of DLookup.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    Thank you so much.!!!

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    Dear Hans,

    Would you help me further? I am sending an attachment.The report is working,if i hardcode it
    to be
    " WHERE orders.customerid = 1008
    If however,i try to use the DLookup function, to find the number 1008 for afid number 1,
    then i get a blank report.
    You could see both cases in the OnOpen event of the report.
    thank you in advance

    regards

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    Is this what your looking for ?

    " WHERE orders.customerid = " & DLookup("StockCustomer", "affiliates", "[afid] = " & Forms!FBenchmark!Office) & " AND Customers.afid= 1 AND orders.orderid > 1295"
    Francois

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup (Access 2000)

    Try

    StrInput = " SELECT orders.orderid, orders.orderdate, orders.paymentid, orders.invoicedate, " & _
    " products.Productid, products.grade, products.size, [order details].cartons, [order details].Quantity" & _
    " FROM ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) " & _
    " INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products " & _
    " ON [order details].ProductID = products.Productid WHERE orders.customerid = " & _
    DLookup("StockCustomer", "affiliates", "afid=" & Forms!FBenchmark!Office) & _
    " AND Customers.afid= 1 AND orders.orderid > 1295"

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    Thank you !!!!
    Please note that the DLookUp function works perfectly now.I even added a new lookup
    copying your syntax,it also works fine and now i have a great program finished!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •