DLookUp for Search Strings in another table...

[kyleno]

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.

 

[Micron]

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.

 

[kyleno]

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.

 

[Micron]

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.