Query to call Date Range parms for a report

[Dreamcatcher]

Hi.

I have tblRecords which contains a 'Period From' & 'Period To' date field which i need to report against.
On Switchboard, i've put a Period Report button which opens a form (frmMultiCalender) allowing operator to enter a TO & FROM date. Currently i have this TO & FROM data stored into a seperate table tblEvents??

From frmMultiCalender i have a button to run the report but i cant seem to write the query correctly.

I want the RptPeriodReport to show just records between the date range chosen (based on the Period To & From records).

Hope that make sense. Seem to be going round in circles at the moment and not sure whether i've gone about this the right way.

Any help would be greatly appreciated to solve my little riddle.

Thanks
John

 

[khawar]

do you want to match exact dates from periodTo and periodFrom fields

 

[raskew]

Hi -

Here's a working example using [OrderDate] and [ShippedDate] from Northwind's Orders table. The operator is prompted to enter the >= [OrderDate] and the <=[ShippedDate].

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate
FROM Orders
WHERE (((Orders.OrderDate)>=[enter From: mm/dd/yy]) AND ((Orders.ShippedDate)<=[Enter To: mm/dd/yy]));

The above was created entirely from the query design-view, without need to write SQL.

HTH - Bob

 

[Dreamcatcher]

Hi,

I need those records whose PeriodFROM and PeriodTO dates both fall within the date range chosen.

 

[raskew]

OK -

Replace the Where statement shown above with

WHERE (((Orders.OrderDate) Between [enter From: mm/dd/yy] And [enter To: mm/dd/yy]) AND ((Orders.ShippedDate) Between [enter From: mm/dd/yy] And [enter To: mm/dd/yy]));

Bob