Results 1 to 6 of 6
  1. #1
    Zoona is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Dec 2016
    Location
    SA
    Posts
    4

    Dlookup multiple criteria

    Hi There,


    I have put multiple criteria, three part criteria in a dlookup function from a table using sql. If I use a combination of any two, it works but not all the three. Any reason why? Does dlookup multicriteria limited to two parameters? The Step into shows the required results. Help please. Thanks

    strWhereSQL = "[PARCEL] = " & intNum & " AND [PART] = " & intpart & " AND [USER_CODE] ='" & strUserId & "'"
    DLookup("id", "tblSearch", strWhereSQL)

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    What are the data types of PARCEL, PART, and USER_CODE?
    Which two work (and which one seems to be the problem)?
    If you could also post a small data sample of your table, it may help.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Syntax appears right. Are you sure you have a record where all 3 values match?

    I created a table of those values and used your code and it returns the ID value when I enter the 3 values from a record onto a form and then run that code.

  4. #4
    Zoona is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Dec 2016
    Location
    SA
    Posts
    4
    PARCEL (integer), PART (integer), and USER_CODE (text/ string)?
    Thanks

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    See if it is actually returning what you expect by adding a MsgBox, i.e.
    Code:
    strWhereSQL = "[PARCEL] = " & intNum & " AND [PART] = " & intpart & " AND [USER_CODE] ='" & strUserId & "'"
    MsgBox strWhereSQL
    DLookup("id", "tblSearch", strWhereSQL)
    Can you post exactly what that returns?

    Also, you did not address the last two items of my previous response.

  6. #6
    Zoona is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Dec 2016
    Location
    SA
    Posts
    4
    Hi JoeM,
    Many thanks for the help. It did work, there was no data in the table that matched that criteria. Trouble was I was creating a table from a query, but I need to update/requery the query to create the table with the new data. I will still answer the questions you asked. I appreciate a lot.

    1) RETURNS... I USED DEBUG.PRINT
    [PARCEL] = 800 AND [PART] = 24 AND [USER_CODE] ='C0230006'

    2) WORKING COMBINATIONS
    strWhereSQL = "[PART] = " & intPart & " AND [USER_CODE] ='" & strUserId & "'"
    strWhereSQL = "[PARCEL] = " & intNum & " AND [PART] = " & intPart & ""

    3) SAMPLE DATA

    ID PARCEL PART USER_CODE
    C02300060000080000024 800 24 C0230006
    C02300230000025000002 250 2 C0230023
    C02300230000025000003 250 3 C0230023
    C02300230000025000004 250 4 C0230023
    C02300230000025000005 250 5 C0230023

    Quote Originally Posted by JoeM View Post
    See if it is actually returning what you expect by adding a MsgBox, i.e.
    Code:
    strWhereSQL = "[PARCEL] = " & intNum & " AND [PART] = " & intpart & " AND [USER_CODE] ='" & strUserId & "'"
    MsgBox strWhereSQL
    DLookup("id", "tblSearch", strWhereSQL)
    Can you post exactly what that returns?

    Also, you did not address the last two items of my previous response.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookup with multiple criteria
    By amai in forum Access
    Replies: 2
    Last Post: 12-20-2015, 02:10 PM
  2. Help with multiple criteria DLookup
    By jtm013 in forum Programming
    Replies: 7
    Last Post: 04-23-2015, 03:16 PM
  3. DLookup with multiple criteria
    By RunTime91 in forum Access
    Replies: 4
    Last Post: 02-08-2015, 08:28 PM
  4. Using DLookup with multiple criteria
    By halt4814 in forum Access
    Replies: 2
    Last Post: 04-08-2013, 12:26 PM
  5. Multiple criteria in DLookup
    By crowegreg in forum Forms
    Replies: 7
    Last Post: 06-22-2011, 01:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums