DLookup In Report only showing on first line

[crimedog]

I have a report based on a query. there is an unbound TextBox that contains a DLookup.
It works perfect on the first line - but is blank on all the other lines.
The query that the DLookup is using does contain the information for all lines.

Any Ideas?

[ranman256]

Don't use DLOOKUP in a query. The qry itself is a lookup.
use a join to pull data from other tables. Hence DLOOKUP.

[crimedog]

the dlookup is in the report

[June7]

Need something to analyze. Post code or db.

[crimedog]

=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.

[June7]

One issue is need a space before the And operator so text doesn't run together in the compiled expression:

& " And [

[crimedog]

Added the space. Still returning blanks - If I add a Date Of Change that is the same month as the DateSerial it fills in the Quota. But I daon't want to add a Quota Every Month - only the months it actually changes.

[June7]

Not understanding this structure. If you want to provide db, follow instructions at bottom of my post.

[crimedog]

Here is an example DB (basically the same)
Open the Choose Quarter form (I forgot to point the Command button to the report)
Then open the Quarter report.

The SubReports have the Dlookup I have been trying to figure out.

[June7]

I would set cboQtr properties:

RowSource: 1;First;4;Second;7;Third;10;Fourth
ColumnCount: 2
ColumnWidths: 0";0.5"
BoundColumn: 1

Filter criteria in query for MODeal: cboQtr

I select First and 2015 on the form and the subreports all show same data. If you want different quarter on each subreport, maybe calculate an adjustment to MODeal:

=[MODeal] + 3
=[MODeal] + 6
=[MODeal] + 9

[crimedog]

That is very much like how I have it in the real DB - I just threw this togethor quickly. What I ultimately want is a Quarter report that breaks out the months of the quarter. For this one I want to choose First Quarter and have it pull out January in subreport 1 February in 2 etc...
This is working - the problem is the Quota - Each rep should always show a quota.
But you see in the Subreport for January only the first rep does - and in the Feb subreport none do.

That is where I was using the DLookup with 2 Criteria. Criterea 1: RepID Criteria 2: the last date that applies to the rep (that is before or equal to the Month (Date Serial))

[crimedog]

Figured it out! the Maxdate needs another Criteria - the way it is MaxDate will return the MAx date regardless of rep - By adding And RepID=Rep the Max Date is now the Max Date FOR THE PARTICULAR Rep