Code:
=DLookUp("[Revquota]","[Qury1TestQuota]","[RepID]=" & [TxtRep_ID] & "And [DateOfChange]=#" & DMax("[DateOfChange]","[Qury1TestQuota]","[DateOfChange]<=#" & DateSerial([TxtDealYear],[TxtMo],1) & "#") & "#")
I have 1 query that returns all the deals that a manager's reps close for a month (which is used for Most of this report)
and a separate query (Qury1TestQuota) that looks up the Quotas for all reps (this can change throughout the year. So each change has a DateofChange
I have it working for the month January - where started each reps quota - Quotas won't be inputted until they change - lets say March.
The Idea is: in February - it finds the last quota input for each rep before Feb 1st (in this case it should be the Jan quotas.
It is just returning blanks.