Dlookup in Query Field with multiple criteria

[Ganymede]

I have a query titled "Query1" that has the following fields: Issue, Engagement and Year. In a separate field in Query1, I want to run a dlookup against a different query titled "Query2," which also contains fields titled Issue, Engagement, year, plus an additional field titled "CaseNo." I want the result of the dlookup to find the CaseNo from Query2 that is in the same row as the Issue, Engagement and Year in Query1. There are no repeats in Query 2. That is, there is no row in query 2 where the same combination of Issue, Engagement and Year are repeated.

Here is the dlookup formula that I created. Note that all of the criteria fields are strings. So I used the string snytax.

DLookUp("[CaseNo]","[Query2]","[Issue] = '" & [Issue] & "'" And "[Engagement] = '" & [Engagement] & "'" And "[Year] ='" & [Year] & "'")

For some reason the result I get is the CaseNo in the first Line in Query 2 where the Issue matches. It's as if the Dlookup is searching for the first row where Issue matches and stopping there, instead of then searching for where Engagement and Year also match. Am I going about this the wrong way? Is there another way to acheive the same outcome? I'm open to any suggestion.

[Gicu]

Your syntax is wrong, please try this:

Case_No:DLookUp("[CaseNo]","[Query2]","[Issue] = '" & [Issue] & "' And [Engagement] = '" & [Engagement] & "' And [Year] ='" & [Year] & "'")

Cheers,

[June7]

Domain aggregate functions can slow performance of query.

Why don't you build query that JOINs on those 3 pairs of common fields?

[Ganymede]

Your syntax is wrong, please try this:

Case_No:DLookUp("[CaseNo]","[Query2]","[Issue] = '" & [Issue] & "' And [Engagement] = '" & [Engagement] & "' And [Year] ='" & [Year] & "'")

Cheers,

Thank you! I couldn't quite get this solution to work. Perhaps I was mistaken in my assumption that all of those fields are string. But I did find a workaround. I basically created several layered queries to get the result I was after.

[Ganymede]

Domain aggregate functions can slow performance of query.

Why don't you build query that JOINs on those 3 pairs of common fields?

That's what I ended up doing. The difficulty is that those fields were spread among multiple tables. But I finally figured out how to do it. Thanks!

[Micron]

IIRC, sometimes domain functions don't like having the object name enclosed in [brackets] when not needed. They are only needed when the field/domain contains spaces, which should never be the case. Same with using reserved words for object names, like Year.

http://www.allenbrowne.com/AppIssueBadWord.html

[June7]

AFAIK, domain aggregate function never has issue with included brackets, needed or not. What is odd is that the domain argument DOES NOT require brackets even if there are spaces/special characters in table/query name.