Hours Available On Date

[D Willett]

hi Guys, a bit stumped with returning hours available on a certain date.

tblAttPeriod holds data for staff member [Unit], branch [Branch] and two dates, [FromDate] and [ThruDate], anything including and in between [FromDate] and [ThruDate] are holiday days booked.

qryStaffCal returns holidays booked on a certain day:

Code: Select all

SELECT qryStaff.UnitID, qryStaff.Unit, tblAttPeriod.FromDate, tblAttPeriod.ThruDate, tblAttPeriod.WorkGroup, tblAttPeriod.PeriodID, tblAttPeriod.Other, qryStaff.HrsAvail
FROM tblAttPeriod INNER JOIN qryStaff ON tblAttPeriod.UnitID = qryStaff.Code
WHERE (((tblAttPeriod.FromDate)<=[Forms]![frmCallCentre]![txtScrollDate]) AND ((tblAttPeriod.ThruDate)>=[Forms]![frmCallCentre]![txtScrollDate]))
ORDER BY qryStaff.Unit;

So on a given day: txtScrollDate, using qryStaffCal, I can see who has booked off for that day and the hrs I will be missing [HrsAvail].

I need to do quite the opposite and see all the hours available excluding the hrs from qryStaffCal on the same day, I'm stumped with this.
Where do I start???

Regards

[HansV]

Try changing

WHERE (((tblAttPeriod.FromDate)<=[Forms]![frmCallCentre]![txtScrollDate]) AND ((tblAttPeriod.ThruDate)>=[Forms]![frmCallCentre]![txtScrollDate]))

to

WHERE (((tblAttPeriod.FromDate)>[Forms]![frmCallCentre]![txtScrollDate]) OR ((tblAttPeriod.ThruDate)<[Forms]![frmCallCentre]![txtScrollDate]))

[D Willett]

Hi Hans, I had the same other than the "OR",this now returns everything excluding FromDate and ThruDate periods ( which is correct ).
I think I need a different table as a base as tblAttPeriod only holds the Holiday periods booked. I'll need to mess about with this a little to achieve the results.

Leave it with me ...

(Thanks :-) )

[D Willett]

Totally different approach.

As the query, qryStaff contains the hrs available for each member of staff, and a series of small queries to return the total for staff and department, using a DSum I am able to return total hours available for each department.

Me.txtHrsAvailValet = DSum(Nz("[Avail]", 0), "qryHrsAvailValet")
Me.txtHrsAvailPaint = DSum(Nz("[Avail]", 0), "qryHrsAvailPaint") + Me.txtHrsAvailValet.Value
Me.txtHrsAvailBody = DSum(Nz("[Avail]", 0), "qryHrsAvailBody")
Me.txtHrsAvailFit = DSum(Nz("[Avail]", 0), "qryHrsAvailFit")

This works in VBA.

From there, I can take away the hours accumulated from the holiday periods so giving me a true sum of hours available.

But is it possible to exclude weekends and bank holidays ??
There is a table in the database, "tblHoliday" containing the Bank Holidays, and a module "modHolidayDatesWorkDays".

It maybe a big ask ....?

Cheers

[HansV]

The query in the first post returns hours for a specific date [Forms]![frmCallCentre]![txtScrollDate]
How does that fit into your current question?

[D Willett]

Hi Hans
The first query qryStaffCal returns hrs depending on the date in txtScrollDate, these are hours booked as holidays so they are not available.

qryStaff returns all hours available, therefore subtracting the qryStaffCal hours should give a true total of hours available on a given date "txtScrollDate".
There are departments, Paint, Fit etc so I have to take that into account as with the above post with the DSums in VBA.

It all works as it should but we have weekend and bank holidays ideally taken into consideration?

[HansV]

What is the SQL of qryStaff?

[D Willett]

Oops .. Have I answered my own question???

Code: Select all

SELECT tblStaff.Code, tblStaff.Surname, tblStaff.Initial, tblStaff.Workgroup, tblStaff.CurrencyRate, tblStaff.ProductivityRate, tblStaff.Active, tblStaff.Address, tblStaff.TelNo, tblStaff.OtherContact, tblStaff.EmployeeType, tblStaff.NINumber, tblStaff.Production, [productivityrate]*8/100 AS HrsAvail, tblStaff.Code AS UnitID, [Surname] & "," & [initial] AS Unit, [hrsAvail]*5 AS [Hrs Per Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[productivityrate]/100 AS [Days This Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[hrsavail] AS [Hrs This Week], Weekdaysminusholidays(DateSerial(Year(Date()),Month(Date()),1),(DateSerial(Year(Date()),Month(Date())+1,0)))*[productivityrate]/100 AS [Days This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Month(Date()),1),(DateSerial(Year(Date()),Month(Date())+1,0)))*[hrsavail] AS [Hrs This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+4,0)))*[productivityrate]/100 AS [Days This Qtr], Weekdaysminusholidays(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+4,0)))*[hrsavail] AS [Hrs This Qtr], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[productivityrate]/100 AS [Days This Year], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[hrsavail] AS [Hrs This Year], tblStaff.Branch, tblStaff.IncludeAtt
FROM tblStaff
WHERE (((tblStaff.Workgroup) Not In ("SignsGraphics","Administration","Sales","Technologies")) AND ((tblStaff.Active)=True) AND ((tblStaff.Branch)=[Forms]![frmCallCentre]![Branch]));

[HansV]

It looks like the hours already take weekends and holidays into account...

[D Willett]

Yes it does .. I need to work out then if txtScrollDate is a weekend or Bank Holiday then no hours are available .. ( example )

Code: Select all

Me.txtHrsAvailValet = DSum(Nz("[Avail]", 0), "qryHrsAvailValet")

Code: Select all

SELECT qryStaff.Workgroup, Sum(qryStaff.HrsAvail) AS Avail
FROM qryStaff
GROUP BY qryStaff.Workgroup
HAVING (((qryStaff.Workgroup)="Valet"));

[HansV]

Perhaps something like

IIf(Weekday([Forms]![frmCallCentre]![txtScrollDate],2)>5 Or DCount("*","tblHoliday","HolidayDate=#" & Format([Forms]![frmCallCentre]![txtScrollDate],"mm/dd/yyyy") & "#)>0,0,...)

where ... is your expression to calculate available hours.

[D Willett]

It looks great Hans, But I'm not sure where to put that ....

[HansV]

Me.txtHrsAvailValet = IIf(Weekday([Forms]![frmCallCentre]![txtScrollDate],2)>5 Or DCount("*","tblHoliday","HolidayDate=#" & Format([Forms]![frmCallCentre]![txtScrollDate],"mm/dd/yyyy") & "#)>0,0,DSum(Nz("[Avail]", 0), "qryHrsAvailValet"))

[D Willett]

Really sorry about this Hans, I'm getting expected List or separator here:

[Avail]

I've checked all the brackets and closing quotes, I can't see it...

[HansV]

I forgot a "

Me.txtHrsAvailValet = IIf(Weekday([Forms]![frmCallCentre]![txtScrollDate],2)>5 Or DCount("*","tblHoliday","HolidayDate=#" & Format([Forms]![frmCallCentre]![txtScrollDate],"mm/dd/yyyy") & "#")>0,0,DSum(Nz("[Avail]", 0), "qryHrsAvailValet"))

[D Willett]

Works like a dream .... didn't think was going to be so complicated

Thank you Hans

Kind Regards

[D Willett]

Just a tweak Hans. i've added at the end: "- Me.txtStaffCalBody" which works fine, however if the result is a minus figure can the result show "" or zero ?
I tried it with Conditional Formatting "If Field Value is Less Than or Equal to 0" but a negative amount still shows??

Me.txtHrsAvailBody = IIf(Weekday([Forms]![frmCallCentre]![txtScrollDate], 2) > 5 Or DCount("*", "tblHoliday", "HolidayDate=#" & Format([Forms]![frmCallCentre]![txtScrollDate], "mm/dd/yyyy") & "#") > 0, 0, DSum(Nz("[Avail]", 0), "qryHrsAvailBody")) - Me.txtStaffCalBody

[HansV]

You could use

Code: Select all

    Dim h As Double
    h = IIf(Weekday([Forms]![frmCallCentre]![txtScrollDate], 2) > 5 Or DCount("*", "tblHoliday", "HolidayDate=#" & Format([Forms]![frmCallCentre]![txtScrollDate], "mm/dd/yyyy") & "#") > 0, 0, DSum(Nz("[Avail]", 0), "qryHrsAvailBody")) - Me.txtStaffCalBody
    If h < 0 Then h = 0
    Me.txtHrsAvailBody = h

Or, alternatively

Code: Select all

    Dim h As Double
    h = IIf(Weekday([Forms]![frmCallCentre]![txtScrollDate], 2) > 5 Or DCount("*", "tblHoliday", "HolidayDate=#" & Format([Forms]![frmCallCentre]![txtScrollDate], "mm/dd/yyyy") & "#") > 0, 0, DSum(Nz("[Avail]", 0), "qryHrsAvailBody")) - Me.txtStaffCalBody
    If h < 0 Then
        Me.txtHrsAvailBody = Null
    Else
        Me.txtHrsAvailBody = h
    End If

[D Willett]

Sounds like a plan batman .... Thanks for the tweak :-)