Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help with DLookup (Access 2003)

    Please help me with a DLookup. The syntax is catching me?? The resulting DLookup value is #ERROR for each row!

    Here is the SQL:
    SELECT Sales.[Date of Sale], [Sales]![Quantity Sold]*[Products]![Retail Price] AS Total, DLookUp("[Last Name]","Customers","[Customers]![Customer Id] =" & [Sales]![Customer Id]) AS CustomerName
    FROM Sales INNER JOIN Products ON Sales.[SKU Number] = Products.[SKU Number];
    Regards,
    Rudi

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

    Re: Help with DLookup (Access 2003)

    I'd use . instead of !

    Why not add the Customers table to the query and join it to Sales on Customer ID? You can then simply include the Last Name field in the query.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Help with DLookup (Access 2003)

    To echo what Hans said, you really should include Customers in your Join. Using DLookup within a query is terribly inefficient, and in a large query will noticeably affect your performance.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with DLookup (Access 2003)

    Are you saying that i must replace the ! with a . ? I did this and it still gives an error!

    I know I can do this. In actual fact it will be a 100x easier to do, but I am trying to learn to use the DLookup function which has always eluded and frustrated me in the past!

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with DLookup (Access 2003)

    Tx for the advice Mark. I am aware of the negatives. As I said, this is more an experiment to learn to use the DLookup for now. I am not planning to use it in the real sense in this context.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

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

    Re: Help with DLookup (Access 2003)

    What is the data type of the Customer ID field (text, number, ...)?

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with DLookup (Access 2003)

    autonumber
    Regards,
    Rudi

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

    Re: Help with DLookup (Access 2003)

    Without seeing the database, it's impossible to know why it doesn't work.
    But if it is just for learning purposes, I'd start with a simpler example. Try a text box on a form with a control source =DLookup(...).

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Help with DLookup (Access 2003)

    You only use ! when showing an item from a collection. Such as controls on a form, etc. You always use '.' when referencing items fields in a table or query.

    Dlookup shouldn't be that difficult: DLookup("LastName","Customers","[Customer ID]=" & Sales.[Customer ID])

    Let's assume you know that you have a [Customer id]=5 in your table. In the Immediate window, type this:

    ?DLookup("LastName","Customers","[Customer ID]=5")

    If this doesn't work, you have specified something wrong.

    BTW, just as a tip, you might try avoiding the use of blanks within field names. You will generally find it makes life a little easier, and will save you some keystrokes, as you won't have to use [ ] all the time.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with DLookup (Access 2003)

    Tx for all the advice...I'll give it another go and reply if I run into any other hitches!
    Regards,
    Rudi

Posting Permissions

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