Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Current Year (A2000)

    I've done this before but lost some notes when changing from Win98 to XP.

    I need to ensure the following SQL only applies to the current Year =>31/03/03

    Our Holidays run from 31/03/0? every year to 01/04 of the next year. So next year when I run this query again, it will only apply to the year it is run.

    SELECT tblUnit.Unit, tblPeriod.FromDate, tblPeriod.ThruDate, [thrudate]-[fromdate]+1 AS Amount
    FROM tblUnit INNER JOIN tblPeriod ON tblUnit.UnitID = tblPeriod.UnitID
    GROUP BY tblUnit.Unit, tblPeriod.FromDate, tblPeriod.ThruDate, [thrudate]-[fromdate]+1
    HAVING (((tblPeriod.FromDate)>=-#3/31/2003#));

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Current Year (A2000)

    Dave
    I have created a function in my DB called Currentyear() where it needs to be other than Jan to Dec - it goes like this:
    Dim Currentyear as Integer
    Currentyear = Year(Now)
    If Month(Now)<4 then Currentyear = Year(Now)+1
    end function
    I can then call it for any special date I wish to create using DateSerial

    Peter

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

    Re: Current Year (A2000)

    Hi Dave,

    You started a thread about this last July; it starts with <post#=159305>post 159305</post#>. The last post in that thread has a database attached that may be useful again...

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

    Re: Current Year (A2000)

    Peter, Thanks for your function.
    I will keep it with my list of utilities for future use.

    Hans
    What a memory !!
    Yes, I do remember this thread and it is exactly what I was looking for.
    Thanks again. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

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

    Re: Current Year (A2000)

    Dave,

    I didn't remember when the thread was, but I *did* remember that something like this had come up before, so I did a search for "holidays" (without the quotes) in this forum.

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

    Re: Current Year (A2000)

    And I thought you were memory man.!! <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

    Regardless, thanks again.

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

    Re: Current Year (A2000)

    Hans
    The attachment from the 159305 post is working brilliant, one thing though.

    I've added a module and a table to account for standard holidays, ie bank holidays,Christmas Day etc.
    How do I enable the report to incorporate this and not count these dates in the total :- "=Sum([Duration])"

    Where the holidays run thru a standard holiday, these should not be added.

    Some staff periodically book a holiday which will tag to a bank holiday so 3 days should only read as 2 !
    Attached Files Attached Files

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

    Re: Current Year (A2000)

    Dave,

    I'll look into it, probably this evening. Perhaps other Loungers will react sooner...

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

    Re: Current Year (A2000)

    I've updated the report to what I thought would work, so as an update here's what I've done so far.

    Thanks again.
    Attached Files Attached Files

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

    Re: Current Year (A2000)

    Dave, there is no need to modify the report at all, it's best to update the queries: wherever the WorkDays function was used, replace it by WeekdaysMinusHolidays. In your database, qryHolidays and qrySelectFromForm are the ones to change. The reports will automatically display the correct duration of the holidays. See attached database.
    Attached Files Attached Files

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

    Re: Current Year (A2000)

    Thanks Hans, it's perfect.

    I've not had time to study the changes yet, but I will.

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

    Re: Current Year (A2000)

    Hans
    Would you mind just checking on the attachment.

    I've added an entry for, "Karen,Willett" 31/03/03 to 02/04/03 which makes 3 days.
    The entry in the table is fine, but the report shows 01/04/03 to 02/04/03 <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> 2 days.

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

    Re: Current Year (A2000)

    There was no attachment when I viewed your post, but that doesn't matter.

    Isn't the result as it should be? If I remember correctly, you wanted to count holidays over periods from the first of April in a year through the last of March in the next year.

    So the entry for Karen is split into two in the report:
    31/03/2003 counts as 1 day in the period 01/04/2002 through 31/03/2003
    01/04/2003 through 02/04/2003 counts as 2 days in the period 01/04/2003 through 31/03/2004

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

    Re: Current Year (A2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Yes you're right, and my wife knows better than to book the last of her holiday's lapped over to the next year.

    I will tell her <img src=/S/argue.gif border=0 alt=argue width=50 height=25> she can't have them because the computer is always right !!

    Lets see the reaction <img src=/S/bif.gif border=0 alt=bif width=70 height=28> from that heh !!

    Wish me luck.

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

    Re: Current Year (A2000)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Don't you know better than blaming your wife for your own mistakes? <img src=/S/wartgun.gif border=0 alt=wartgun width=73 height=24>

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
  •