Query with DLookUp and "Or" stements in the return

[CDRCHOP]

I am having trouble with a simple query using the DLookUp function. I am using the DLookUp function to look up values in a field in a "temp" table (holds a single record of data that changes). The DLookUp function works fine when the "temp" table field I am performing the lookup has a single name. I also have instances when the field will be populated with multiple names with an "Or" between the names. For example, the query works fine when there is just one name..."Name1"...but if I have the field in the "temp" table showing text of ("Name1" Or "Name2") then the result doesn't work. I think the DLookUp result is only seeing this entire string as pure text and not separating the text as "Or" statements. Is there a way to force the query to see this as "Or" statements vice pure text?



I am trying to get away from using more complicated logic of separating the string into smaller pieces and using the "Or" statement in between multiple names.

Thank you!

[ranman256]

Do NOT use Dlookup in a query. The QUERY is the Dlookup.
you do this by joining your data table to the lookup table.
the criteria is either in a form or the query.

[CDRCHOP]

Not sure what you mean. I have been using Access since the mid 90's and I routinely use DLookUp in queries. I have a unique instance where I have a single field that holds a name but in 2 instances the field holds multiple names separated by "Or" and I am trying to figure out a way for the DLookUp function to recognize this as an Or statement. Example...."Name1" Or "Name2" vice "Name1 or Name2." I can manually copy and paste the field contents into the query and it works fine but when using the DLookUp function it sees the entire string as text and of course doesn't work as intended.

[June7]

Maybe:

DLookup("somefield", "sometable", "fieldname LIKE '*" & [fieldname] & "*'")

or

DLookup("somefield", "sometable", "InStr(fieldname, '" & [fieldname] & "')>0")