need to test a range of dates (1 Viewer)

[ANDREW_SAUNDERS]

I have a holiday database where [1stday] and [Lastday] are entered
I need to find all records that have a date within a certain week with a date range of Sunday to Saturday

I have a problem with my current idea in that if one or both of [1stday] or [Lastday] are out side the selection criteria then they are not selected ie a holiday of 14 days !!!
Any Sugestions?

 

[ColinEssex]

Hi
Can you be a bit more specific? I'm a bit confused as to what you are trying to achieve. Do you want to find out who is away between certain dates?

Col

 

[ANDREW_SAUNDERS]

hi
you have the idia
I have to produce a list of personel who are on holiday each week
my current idea works ok when the one or both of the two dates fall within the selected week but if they span it it fails to select them !!!!!
I an trying to make a function to test each day betwen the start and finish of the holiday to see if they match the selected week
regards Andrew

 

[ColinEssex]

Hi

I've actually done this 2 different ways.

Easy way

Create a query with whatever fields you need - Forename,Surname,StartDate,EndDate
Then in the query criteria you need to enter the following under the startDate field
StartDate
>=StartDate
or <StartDate
or >=StartDate and <=EndDate

and this under the EndDate field

EndDate
<=EndDate
or >=StartDate
(leave line 3 of endDate blank)

If you're referring to a form it'll be Forms!FormName!StartDate etc etc

Harder Way

Create some code to insert into a table a new line for every day of the persons leave period. You'll need a For-Next loop to do the update. Then your date range will search the table correctly.
If you're really keen to go down this road - email me and I'll send you the code of how it's done.

Hope this helps
Col