Query Parameters

[Guest]

I am currently setting up a database for managing sickness absence. The dates
are recorded as follows:
1st date of Absence
Last date of Absence
Number of days Absent

1. I will want to query the number of days absence in the last 6 months
which will also pick up any periods that overlap that 6 month date e.g. if
today is 25/10/07 and somebody was sick from 19/4/07 - 25/4/07 it would pick
up that period as well

2. Additionally, i need to be able to query the number of instances of
absence in 6 months that will do the same

Any great if statements or SQL would be appreciated - i've always had
problems understanding how you can get access to look at dates in between
periods
Much Obliged to anybody who can help

 

[John Spencer]

To identify records in the period you need something like

WHERE FirstDay <= PeriodEnd And LastDay >= PeriodStart

In the query grid
Field: First Date of Absence
Criteria: <= Date()

Field: Last Date of Absence
Criteria: >= DateAdd("m"-6,Date())



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================