Exclude weekends

[tezread]

I have query where a column has a start date and end date. I wanted to be able to work out the days taken for each episode but take off days where weekends are encountered?

Best wishes

Tez


SELECT tbl_Data.EpisodeID, tbl_Data.Outcome, tbl_Data.ReferringHospital, tbl_Data.ReceiveHospital, Int(IIf(tbl_Data!DaysRequestToProcedure1<0,"0",IIf(tbl_Data!DaysRequestToProcedure1>=12,"12",tbl_Data!DaysRequestToProcedure1))) AS Days, tbl_Data.DaysRequestToProcedure1, tbl_Data.RequestProcedureType, tbl_Data.Procedure1, tbl_Data.Procedure2, tbl_Data.ProcedureN, tbl_Data.RequestDatetime, tbl_Data.Procedure1DateTime
FROM qry_surgery RIGHT JOIN (qry_NoProcedure RIGHT JOIN tbl_Data ON qry_NoProcedure.EpisodeID = tbl_Data.EpisodeID) ON qry_surgery.EpisodeID = tbl_Data.EpisodeID
WHERE (((tbl_Data.Outcome)="Completed") AND ((qry_surgery.EpisodeID) Is Null) AND ((qry_NoProcedure.EpisodeID) Is Null));

 

[Brianwarnock]

A search will reveal that this type of question has been asked and answered before.

Brian

 

[tezread]

I have done a search a found a solution!

WorkingDays: IIf(Weekday([RequestDateTime])>Weekday([Procedure1DateTime]),1+Int(([Procedure1DateTime]-[RequestDateTime])/7),Int(([Procedure1DateTime]-[RequestDateTime])/7))*5+Weekday([Procedure1DateTime])-Weekday([RequestDateTime])


I was wondering though - how could I customise this so the query also adds a condition that if the result is >=12 it is '12' and if it is less than '0' it is '0'

I have this for another field:

Days: Int(IIf(tbl_Data!DaysRequestToProcedure1<0,"0",IIf(tbl_Data!DaysRequestToProcedure1>=12,"12",tbl_Data!DaysRequestToProcedure1)))

 

[Brianwarnock]

With your comple IIf its difficult, I would have tested Raskew's function, the first hit in my search as it then makes it easy to wrap an iif around it if you cannot alter the code yourself.

Brian

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
're:        http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'Coded by:  raskew
'Input:    ? fGetWorkdays2(#2/10/06#, #7/13/06#)
'Output:    110
'************************************************

   fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1

End Function