Date expressions for use in query

[wordsmith]

Hi all,

I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.

However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.


thanks in advance!!

 

[wordsmith]

hi all,

been thinkin bout this, would really appreciate any feedback from the much wiser than me.......Would something like this work?

An expression that would use BETWEEN...AND function? i'm not sure how to write it so that it does this:

Return all records in the Holidays tbl where the days between the StartDate (fld) and EndDate (fld) of the holiday is within the WeekBegin (date fld) and WeekEnd (i figure this will be a calculated field using the dateadd function?) for the selected week taken from the week tbl.

much appreciated!!

 

[Brianwarnock]

OK you have a time period weekbegin to weekend and a holiday period startdate to enddate.
I think that you need to check for 3 possible scenarios

1 startdate >weekbegin and < weekend
2 enddate > weekbegin and < weekend
3 startdate < weekbegin and enddate > weekend

Brian

 

[Brianwarnock]

Correction

You will need some = in those expressions

Brian


brian

 

[wordsmith]

Thanks for that Brian, I came up with some other scenarios e.g.

startdate >weekbegin and > weekend

I was wondering if there is anyway to define those dates in between the StartDate and EndDate? likewise for the dates between the WeekBegin and WeekEnd dates?

In which case would I then just be able to perhaps create and expression that would just match any of the defined holiday dates with the corresponding week?

much appreciated!!

 

[Brianwarnock]

startdate >weekbegin and > weekend

Surely this means that the holiday was after the period that you are checking?

I was wondering if there is anyway to define those dates in between the StartDate and EndDate? likewise for the dates between the WeekBegin and WeekEnd dates?

Not sure I understand this, your SQL will look something like, with your field names of course,

where ((startdate Between weekbegin AND weekend) Or (enddate Between weekbegin AND weekend) Or (startdate < weekbegin AND enddate > weekend))

This covers the scenarios of the holiday starting within the period, ending within the period or neither but spanning the period.


Brian

 

[wordsmith]

Brian,

ignore me, I made a mistake there. Thanks for your help!

One thing though, I created a few calculated fields which uses the results of that expression to calculate the total days taken. I've been using DateDiff to do this (but had to add 1 to all the expressions as it doesn't include the start date), any suggestions of a different expression? Also I've had to add that it takes away the number of Weekend days in the holiday as they don't count(i created a field in the Holiday tbl to do this). This all seems to work fine.

But when I want to work out the Days taken in the selected week, I can't figure out how to make it know which part of the days taken falls within the selected week excluding the weekend within the full holiday.

I know there is a way to label the days e.g. monday = 1 sunday = 7, im not sure how, but would this perhaps be useful?

Thanks sooooooooo much for your help to a desperate novice.

Cheers!
Keji

 

[wordsmith]

Brian,

ignore me, I made a mistake there. Thanks for your help!

One thing though, I created a few calculated fields which uses the results of that expression to calculate the total days taken. I've been using DateDiff to do this (but had to add 1 to all the expressions as it doesn't include the start date), any suggestions of a different expression? Also I've had to add that it takes away the number of Weekend days in the holiday as they don't count(i created a field in the Holiday tbl to do this). This all seems to work fine.

But when I want to work out the Days taken in the selected week, I can't figure out how to make it know which part of the days taken falls within the selected week excluding the weekend within the full holiday.

I know there is a way to label the days e.g. monday = 1 sunday = 7, im not sure how, but would this perhaps be useful?

Thanks sooooooooo much for your help to a desperate novice.

Cheers!
Keji

 

[wordsmith]

I'm really stuck on this query, help desperately needed.

Does this look right? any guidance on where I'm going wrong because every time I enter the WeekDesc, it still only brings up the Holiday Record where the Week for that record matches.

It needs to bring up the other records if their dates crossover into the selected week even if their week field doesn't match

any assistance much appreciated!!

Regards

Keji