11pm to 11 pm

[Doc94]

OK complex problem I have not been able to get to the bottom of. I have a query that needs to pull out records for a time period as follows:

SELECT [11-7_tally_report].DispositionVehicle, [11-7_tally_report].[ED#], [11-7_tally_report].Pediatric, [11-7_tally_report].Adult, [11-7_tally_report].FacilityCode, [11-7_tally_report].HospitalArrivalTime
FROM [11-7_tally_report] INNER JOIN ReportDates ON [11-7_tally_report].FacilityCode = ReportDates.FacilityCode
WHERE ((([11-7_tally_report].HospitalArrivalTime) Between [BegDate] And [EndDate]) AND ((IIf([11-7_tally_report]![Time]=15 Or [11-7_tally_report]![Time]=16 Or [11-7_tally_report]![Time]=17 Or [11-7_tally_report]![Time]=18 Or [11-7_tally_report]![Time]=19 Or [11-7_tally_report]![Time]=20 Or [11-7_tally_report]![Time]=21 Or [11-7_tally_report]![Time]=22,3)) Like "3&quot);

[BegDate] and [EndDate] are stored in a table. I need the default values in the table to be fromm 11pm two days ago to 11 pm last night I have tried setting the tables default value to (date()-25/24) and (date()-2/24) for BegDate and EndDate respectively. And it will work as it gives me from 11pm 2 days ago to 11pm last night. But then it just holds that one record which can not be used by the query. How can I have it update daily so it can be used by the query? Or is there another solution?

 

[jiqjaq]

Forget about storing the BegDate and EndDate in a table. Take the calculations you used for those default values and place them inside the query itself where you now reference [BegDate] and [EndDate]. The calculation needs to take place when the query is executed, not beforehand.

 

[Doc94]

Thanks jigjag. I could do that but the dates are also used in a report. I have a report with a number of subreports based on this as well as other similar queries. I use the Trim function for the title of the report. =Trim("For the Period" & [begdate] "to" & [enddate])

 

[Doc94]

jigjag-
If I enter the parameters into the query, can they then be passed to the report?