Need Subreport date range based on record (1 Viewer)

I am working on a data base for a trucking company. What I'm having trouble with is a report that will show the information from a job and expenses that were incurred during the job. I have my job information on one table [CompCtbl] and I have the fields [PuDate],[DelDate],[Truck], (I have more fields but this is what I think is needed for my question].
On another table, I have company expenses that the costumer does not get itemized billed for. Table [Ex] with fields [EqmDate],[EqmTruckNum],[EqmTotal].

I have set up a Report bound to a query that ask for a start and end date to filter the records from the [CompCtbl] based on a Between statement on [DelDate]. So far the report is showing me what I want, All records from [CompCtbl] within the dates inputed in the system popup box.

Then I tried inserting a subreport in the detail section under the textboxes of the record linking [Truck] with [EqmTruckNum]. This is where I get half of what I want. I get my main record with a subreport under it with the expense records of the associated truck. However it shows every expense record for truck in the subreport. How can I code the subreport to pull from a date range based on my [PuDate] and [DelDate] for each record while keeping my link by Truck.

Thanks in advance!

Instead of binding the subreport to the table, create a query that has selection criteria to select only rows that satisfy the date range. Use that query as the RecordSource for the subform.

Select ...
From YourTAble Where SomeDate Between Forms!yourform!PuDate And Forms!yourForm!DelDate;

this can't be done
using your main report's
field as criteria to your
subreport's query recordsource
since the subreport load
first than the main report.

use tempVars, as used in the
attached example.

set the tempvars on main report's
Activate Event.

set the filter of the subform on
it's Open event. be sure to ignore
error (On Error Resume Next) since
we are sure Main report is not
finished loading.

Thanks Pat and Arnelgp.
Pat- When I tried your method I get a blank sub report and I get a popup box for each record asking for the parameters for the sub report.

Arnelpg- I really appreciate the example! but it is not doing what I need it to do. I went in and played with it a little as my main report the pudates and del dates are not the same for each truck. For example in a weeks time one truck might have 5 pu's and del's in the week (one each day) while others might have 2-4 day length trips. Since some trips go across the weekend I was trying to set up a between criteria on the main report so I could manipulate the data as needed and also maybe maybe change to a two week or month selection on the fly. Then I was trying to have the subform of the expenses that fell with in the date range of the parent record.

So I would have some thing like this

pudate deldate truck description ........RATE
9/6...... 9/8 .......1 ....Dallas-Denver ..1600
..EqmDate EqmTk Expense CHARGES
..9/6........... 1 ......Diesel .........346
..9/7 ...........1...... TOLL ............11
..9/8........... 1...... LUMPER........ 50


PuDate DelDate Truck Description ..........Rate
9/8...... 9/13....... 1... Denver-ATLANTA ..2500
..EqmDate EqmTk Expense Charges
..9/8 ............1 ....Diesel......... 416
..9/10.......... 1.... Diesel .........212
..9/13.......... 1 ....Tire............ 250

PuDate DelDate Truck Description......... Rate
9/7...... 9/14...... 2 ....DAL-PGH-DALL... 4000
..EqmDate EqmTk Expense Charges
..9/8............2......Diesel .........416
..9/8........... 2..... TOLL.............. 5
..9/10......... 2 .....SCALE........... 12
..9/13......... 2..... RdRepair ......846
..9/14......... 2..... Lumper ........100

on my sample db, remove all the code
on the Main Report and Sub Report

Now Paste this on Sub Report's
Open Event:

this can't be done
using your main report's
field as criteria to your
subreport's query recordsource
since the subreport load
first than the main report.

Click to expand...

Yes it can. While it is true that the subform loads first, Access reruns events for subforms a second time after the main form loads. Otherwise, master/child links couldn't work to filter the subform data.

Pat- When I tried your method I get a blank sub report and I get a popup box for each record asking for the parameters for the sub report.

Click to expand...

Then you did not implement the form reference correctly in the query. Please post the SQL you used.

What are START and END? If you want to refer to form fields, you need to use the correct reference style

Forms!yourformname!yourcontrolname

Sorry about that; START and END is what is in the query right now for me to select the dates, I've been playing with it (without success) still trying to make it do what I want. I'll need to go back and redo to get it back to the state I had before to try your code again.

SELECT Ex.EqmDate, Ex.EqmTkNum, Ex.CCatagory, Ex.[1120Cat], Ex.EqmDescription, Ex.EqmTotal
FROM Ex
WHERE ((([Ex.EqmDate]) Between ([Forms]![AcctPayDrEst]![PuDate]) And ([Forms]![AcctPayDrEst]![DelDate])));

gives me a blank subreport

Start off in small steps.
Build your query in design mode.
Use [yourcriteria]in the relevant fields where needed to get it working.
Then replace those with the form controls using the Build option

That way you should be able to pinpoint where the problem lies.

That is how I generally get to my errors.

Gasman said:

Start off in small steps.
Build your query in design mode.
Use [yourcriteria]in the relevant fields where needed to get it working.
Then replace those with the form controls using the Build option

That way you should be able to pinpoint where the problem lies.

That is how I generally get to my errors.

Click to expand...

That’s kind of what I have done. I have one point got it to where I have my date range on a query for my report. And then I had query for my sub report asking for a date range. What I ran into was that the subreport query would asked me to enter The date range for the sub report after each main record but it did give me what I wanted.. I was hoping that there was someway to automate that it would pick up my dates from the primary record for the sub report immediately following it and then repeat the process for the next primary record and sub report. In searching for possible fixes I’ve bumped into RecordsetClone but I’ve never use that before and don’t know if that would be the answer to my problem. So that my underlining goal is that I have Primary records with a date range and Each primary record has a subreport to have the individual records of a single day falling in that date range.

I think I got it!!!
On the SubReport Query to have the date from the table I wanted and then put the main table as it's criteria by use of "Int". And then have my Parent and child linked by Truck#

SELECT Ex.EqmDate, Ex.EqmTkNum, Ex.CCatagory, Ex.[1120Cat], Ex.EqmDescription, Ex.EqmTotal
FROM Ex
WHERE (((Ex.EqmDate) Between Int([CmpCTbl]![PkUpDateCmpC]) And Int([CmpCTbl]![DeliveryDate])+0.99));

Thanks everyone for your inputs to help me with this!