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

    DLookup (Access 2000)

    (Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)

    I refer to the <post#=457602>post 457602</post#> where i had sucessfully solved the problem. I did however encounter a problem with a similar arrangement on another report. There i receive an error and i cannot find the answer. I am trying to get the results for the option 2 from the main form, using a DLookup function to find the record on the table tblStock. I am also attaching the abridged database. Could you please help me?

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

    Re: DLookup (Access 2000)

    You need parentheses in the FROM clause of the SQL statement in the On Open event procedure of the report. You forgot to include orderid in the query.

    StrOutput = "SELECT orders.orderdate, orders.orderid, 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 Customers.afid = [Forms]![FBenchmark]![Office] And orders.orderid >= " _
    & DLookup("LastOrder", "TblStock", "[ID1] = " & Forms!FBenchmark!Office)

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

    Re: DLookup (Access 2000)

    Dear Hans,

    Thank you so much i have seen your remarks they are perfectly right.
    I still have difficulties especialy in the where clause and i get red colors
    in my code,obnviously something is not right.I have tried toi put an " for th new line beginning

    with DLOOKup, last line, buit still i cannot get it straight.
    Will you please have a look at the following code,which is not working :

    StrOutput = "SELECT orders.orderdate, orders.orderid,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 Customers.afid = [Forms]![FBenchmark]![Office] And " orders.orderid >= " _
    "& DLookup("LastOrder", "TblStock", "[ID1] = " & Forms!FBenchmark!Office)

    Thank you in advance for the great insight and help i have received.

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

    Re: DLookup (Access 2000)

    Why don't you use the code I posted?

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

    Re: DLookup (Access 2000)

    Dear Hans,

    I have seen my mistakes and of course you were right.I have corrected all that i want to inform you that now i have finished it
    and my code is great!

Posting Permissions

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