DLookUp for Search Strings in another table...
Results 1 to 4 of 4

Thread: DLookUp for Search Strings in another table...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DLookUp for Search Strings in another table...

    I am trying to do a lookup function in Access as I would use a Vlookup in Excel. I found the Dlookup expression but cannot seem to get it to work properly. I would like to use an expression and not VBA if at all possible.

    I would like the expression to reference the below table (titled Search Strings) to insert the value from the "Current_Search_String" column into the criteria of another query.

    Note: My character count will be below the 1024 limit in query design.

    Search Strings
    Attr_7 GUD Account_Name Current_Search_String
    B72 123456789 ABC Company like "*abc*corp" or like "*abc*comp*" or like "*cba*" or like "acme *"
    C72 987654321 XYZ Inc like "*xyz*corp" or like "*xyz*comp*" or like "*my company*"

    I have tried the following expressions and none have worked...


    DLookUp("[Current_Search_String]","[Search_Strings]","[Attr_7]=b72")


    DLookUp("[Search_Strings]![Current_Search_String]","[Search_Strings]","[Search_Strings]![Attr_7]"="b72")


    DLookUp("[Search_Strings].[Current_Search_String]","[Search_Strings]","[Search_Strings].[Attr_7]"="b72")


    Please and thank you.

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,673
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DLookUp for Search Strings in another table...

    you cannot use DLookup to lookup values based on a sql statement or any part of such as statement. The first parameter must be a field; the second must be a domain. The domain can be a table or query.
    to insert the value from the "Current_Search_String"
    Not sure what that means. The function cannot insert either, and it can only return one value, so I don't get the rest of the post - especially the use of LIKE.

    Maybe provide a much clearer explanation of what you have and what you need to do and forget about what doesn't work for now.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DLookUp for Search Strings in another table...

    Let me start over; we have a table that contains our values that we want to use as our Criteria for the query. We want to have the Criteria field in the query reference a field in this table (ideally using a DLookup but I'm open to other suggestions if there's an easier way).

    So one value in the table is:

    like "*abc*corp" or like "*abc*comp*" or like "*cba*" or like "acme *"

    and we want this to be used as the criteria for the query.
    Last edited by kyleno; Jul 3rd, 2019 at 02:50 PM.

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,673
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DLookUp for Search Strings in another table...

    I believe I have seen this before and the determination was that you cannot use a string from a table as criteria in a query. If you create a table with just one where clause string and a new query, and in the query build a calculated field like Test: DLookup("fieldName", "tableName") and run that query against a table that contains the values, you can get at least one record as long as the criteria string is valid and the lookup can return it. If you remove that field and shift that expression to the query criteria row for the field that the same criteria string should work on, it doesn't work.

    If getting the criteria from a form is not practical then I suspect you would have to lookup the criteria in code and concatenate it into a valid sql statement and run it via vba.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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