Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Dates (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates (A2k)

    Does anyone possess an information paper or word doc etc, showing a full range of Date expressions to use in queries.

    ie

    First Date of current week
    Last Date of current week

    First Date of current month
    Last Date of current month

    First Date of Each Month
    Last Date of Each Month

    etc etc

    I need to quite a lot of calculations in queries regarding Hrs, Days, Weeks, Months, Years etc.
    An information paper would be extremely useful.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Dates (A2k)

    I don't know of a comprehensive reference, but this MSKB article summarizes many of these calculations:

    ACC2000: Functions for Calculating and Displaying Date/Time Values

    HTH

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    Thanks Mark.

    This is very useful.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    Mark
    The samples provided are very good but just a further question.

    How do I use them in a query.

    I have a list of employees showing there productivity rating, ie 25%, 50%, 100% etc.
    This is the easy bit,
    "HrsAvail: [productivityrate]*8/100" which gives the hrs available per day.
    "Hrs Per Week: [hrsAvail]*5" which gives the hrs available per week.

    The complicated part starts from here:

    How to calculate per month
    How to calculate per quarter
    How to calculate per Year

    I must take into consideration Standard Bank Holidays etc which I have defined in a module "modWeekDaysMinusHolidays"
    So this in fact makes the weekly calculation as above none functional.

    My query is as follows:-

    SELECT qryStaff.Unit AS Employee, qryStaff.ProductivityRate AS [ProductivityRate in %], qryStaff.HrsAvail AS [Hrs Per Day], [hrsAvail]*5 AS [Hrs Per Week]
    FROM qryStaff
    WHERE (((qryStaff.Production)=True) AND ((qryStaff.Active)=True));

    Some indication as to how this should be with the interaction of the module would be really helpful.
    The attached PNG shows how I am trying to achieve the basic layout, for use later on in future calculations.
    Attached Images Attached Images

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 09-Sep-03 17:40. Expression Found (bottom of post)
    )</P>The expression as follows, looks specifically designed for this process, but how can it be manipulated to suit my purpose.

    WeekDaysMinusHolidays([Date1],[Date2])

    I do not have a Date1 & Date2 within the query.
    My need would be

    WeekDaysMinusHolidays([StartOfWeek],[EndOfWeek])
    WeekDaysMinusHolidays([StartOfMonth],[EndOfMonth])
    WeekDaysMinusHolidays([StartOfQuarter],[EndOfQuarter])
    WeekDaysMinusHolidays([StartOfYear],[EndOfYear])

    Does this further explanation help to describe what I need ?

    Post edited below:




    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()),Mont h(Date()),1),(DateSerial(Year(Date()),Month(Date() )+1,0)))*[productivityrate]/100 AS [Days This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Mont h(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]
    FROM tblStaff;

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Dates (A2k)

    I had a chance to look at this, my recommendation would be to create some user-defined functions to calculate the number of workdays in current week, month, quarter, and year, and use these in the queries that calculate the work-hours based on employee's productivity rate. Examples:

    Public Function GetWorkdaysThisWeek() As Integer

    GetWorkdaysThisWeek = GetWorkDays(Date - Weekday(Date) + 1, _
    Date - Weekday(Date) + 7, True)
    End Function

    Public Function GetWorkdaysThisMonth() As Integer

    GetWorkdaysThisMonth = GetWorkDays(DateSerial(Year(Date), Month(Date), 1), _
    DateSerial(Year(Date), Month(Date) + 1, 0), True)
    End Function

    Public Function GetWorkdaysThisQtr() As Integer

    GetWorkdaysThisQtr = GetWorkDays(DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1), DateSerial(Year( _
    Date), Int((Month(Date) - 1) / 3) * 3 + 4, 0), True)

    End Function

    Public Function GetWorkdaysThisYear() As Integer

    GetWorkdaysThisYear = GetWorkDays(DateSerial(Year(Date), 1, 1), _
    DateSerial(Year(Date), 12, 31), True)
    End Function

    Example of results:

    ? GetWorkdaysThisWeek
    5
    ? GetWorkdaysThisMonth
    21
    ? GetWorkdaysThisQtr
    64
    ? GetWorkdaysThisYear
    251

    These functions use some of the formulas provided by MSKB article, and a user-defined GetWorkDays function to calculate number of workdays in specified period. Holidays are taken into account using a GetHolidayCount function. See attached text file to see code used for these functions. They may be similar to what you are using. You'd have to change the table & field names in GetHolidayCount function to reflect the holiday table you are using. Using functions shown above should simplify the syntax used in query. You'd just multiply number of days returned by function times number of hours available per day for the employee (I assume this value or the percentage used is stored in one of the tables used in query).

    If some of the employees are only producing at 25% productivity, unless they're part-timers they should get the sack.

    To import the attached text file as module, change file extension to ".bas", then import from VB Editor (File>Import File command).

    HTH
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 10-Sep-03 18:39. Additional question relating to same post added at the end.)</P>If some of the employees are only producing at 25% productivity, unless they're part-timers they should get the sack.

    Mark, Steady on a bit !!

    I'll explain:-
    A 25% operative does have a valuable meaning.
    An operatives Productivity is not the same as their Efficiency.

    An operative with a 100% Productivity rating means he is fully skilled, His Efficiency is calculated by Time Given Multiplied by Time Taken.
    Basically because of his skill, if he is given a 10hr task, he can achieve it in 10hrs or less.
    He recieves a fully experienced hourly staff rate.

    A 50% operative is semi skilled, give him a 10hr task, he will complete it in 20hrs or less, and recieves only 50% of a fully experienced hourly rate.

    A 25% operative is a new comer with very little skill, mainly apprentices and the rest should be clear as to his salary and status.

    Each operative has his own target to achieve.
    If he wants more salary, he must learn more to achieve more, the better the efficiency, the higher the productivity.
    Pretty basic really.

    This is where all these calculations come into affect.

    To load the workshop, we need to know how many hrs are available given the amount of operatives.

    So, 3 fully skilled would mean 8hrs each for each day, giving 24hrs per day I can book work in.

    Now, having 2 x 50%ers would mean I could book 4hrs each operative per day.

    2 x 25%ers would mean 2hrs booked in per day.

    So 3 x 100%ers, 2 x 50%ers, 2 x 25%ers would give an accurate 36hrs available each day.

    You can probably tell by this the reason for these expressions I needed.

    I have the SQL query above which I can hold a constant availability of hrs. I am hoping to be able to pick info from this for date ranges, but have I made the correct decision here.???
    This is the ultimate question before I proceed.

    Thanks for the information as above anyway.

    A further question, How to remove breaks ?
    Our staff work 08:30 to 17:15, without the break, this gives 8hrs, with break 8.75hrs.
    How does the expression read to accomplish this:-

    WeekDaysMinusHolidays(From,To)-.75hr ???

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (A2k)

    Are you still looking for answers, or did you solve this?

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    Hans
    I managed to (I think) do what I set out to do, ie:

    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()),Mont h(Date()),1),(DateSerial(Year(Date()),Month(Date() )+1,0)))*[productivityrate]/100 AS [Days This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Mont h(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]
    FROM tblStaff;

    But never accounted for the breaks, 45minutes lunch.
    I had further thought on this also which came to light the other day.
    All my equations are based around an 8 hr day. One of our operatives only works 30hrs per week and I haven't accounted for overtime ie > 8hrs per day or > 40hrs per week.
    At the moment I a m on another part of the database so I've shelved this part for later.

    It would be nice though to see an example expression of:

    [TimeEnd]-[TimeStart]-[BreakTime]

    08:00 to 17:15 = 9.15 less 0.45 break = 8hrs
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (A2k)

    How do you get from 9.15 less 0.45 break to a result of 8 hours?

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    Shhhhhhhhhhhhhh

    The lads on the shop floor have never worked that one out <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    (This will cost me thousands if they find out.)

    It should have been 08:30 to 17:15

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    Hans
    As ever, <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> , Nice to see you back in the seat <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    Hans
    Just had chance to try the two equations and this the result.
    Any Idea's ?
    Attached Images Attached Images

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (A2k)

    <P ID="edit" class=small>(Edited by HansV on 26-Sep-03 17:12. Original version had DateSerial instead of TimeSerial.)</P>Since Access stores and calculates dates and times in units of 1 day, something like [End Time] - [Start Time] - 0.75/24 should work. Or use [End Time] - [Start Time] - TimeSerial(0, 45, 0). Of course, if these are expressions in the control source of a text box, you should precede them with an = sign.

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2k)

    This will not do !! <img src=/S/scold.gif border=0 alt=scold width=50 height=15> <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    Only Kidding, Thanks <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Attached Images Attached Images

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •