Query Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
'====================================================
 
Back
Top