Dlookup Multiple Criteria

[AndrewKent]

Hi there,

I have a table in Access that I am trying to lookup if two criteria are met.

The table I want to look up is "tblDefinitions_MonthEnd"

The field I want to return from this table is "MonthEnd_LastDate"

If "MonthEnd_Week" in the table matches "CL_Week" in my query

AND

If "MonthEnd_Year" in the table matches "CL_Year" in my query

Can anyone help structure this?

Andy

 

[Swift_74d]

=dlookup("[MonthEnd_LastDate]","[tblDefinitions_MonthEnd]","[tblDefinitions_MonthEnd]![MonthEnd_Week]='" & [CL_Week] & "' AND [tblDefinitions_MonthEnd]![MonthEnd_Year] =" & [CL_Year])

should work assuming that [CL_Week] is text and [CL_Year] is a number

 

[AndrewKent]

Thanks, okay so I managed to complete the expression I wanted:

Tenure Banding: DLookUp("Tenure_Banding","tblDefinitions_Tenure","Tenure_MonthsService=" & DateDiff("m",[CL_StartDate],DLookUp("MonthEnd_LastDate","tblDefinitions_MonthEnd","MonthEnd_Week = " & [CL_Week] & " and MonthEnd_Year = '" & [CL_Year] & "'")) & ""

Which works, HOWEVER when access this again, it tells me I'm missing a ] or |

Does anything look missing here?

Andy

 

[Swift_74d]

Tenure Banding: DLookUp("[Tenure_Banding]","[tblDefinitions_Tenure]"," [tblDefinitions_Tenure]![Tenure_MonthsService]=" & DateDiff("m",[CL_StartDate],DLookUp("[MonthEnd_LastDate]","[tblDefinitions_MonthEnd]"," [tblDefinitions_MonthEnd]![MonthEnd_Week]=’" & [CL_Week] & "’ and [tblDefinitions_MonthEnd]![MonthEnd_Year]= " & [CL_Year] )))

Should work, its hard telling without knowing the data formats for each...let me know if it fails and how..

 

[AndrewKent]

Ahhh figured it out, there was nothing wrong with my expression, I built it BEFORE I put on grouping, then tried to access it WITH group on. then it caused problems. I'll just not access it in builder mode with grouping on.