one parameter dependent on another

[Guest]

We are trying to generate a query which will be used as the basis for an
upcoming report. The query needs to select records from within a date range
but the date range is dependent upon another field (TruckID). The report
will compile information grouped by TruckID. The date range for the records
selected to calculate the information for one field of the report will vary
for each TruckID. The beginning and ending dates will be stored in the
Trucks Table. How do we create the query that will get the appropriate date
ranges for each TruckID as the report groups them? I know how to create the
query where you supply these parameters, but how do you get the parameters to
lookup the appropriate date ranges from the Trucks table relative to the
truck information within the report? Thanks for any suggestions.

 

[Guest]

Create the query in design view adding both tables. Left join on TruckID to
the truck-date range table (just in case someone omitted a truck from the
table).

For criteria use IIF([Trucks Table].[beginning] Is Not Null, Between
[Trucks Table].[beginning] and [Trucks Table].[ending], Like "*") Or Is Null