DLookUp for Search Strings in another table...

kyleno

Board Regular
Joined
Jun 9, 2014
Messages
61
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top