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

    1/2 Days (Access 2K)

    In one of my reports, we have to account for 1/2 days sometimes.
    At the moment we only have the facility for full days.
    So If some-one has 20 days off and two of them are really 2 half days, the report will read 22.
    Is there a way on formatting the report to recognise the string in the text (Field) which contains "1/2" and reduce the total by 0.5 ?

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

    Re: 1/2 Days (Access 2K)

    Hi Dave,

    Can you provide a bit more information about how the total is calculated now? That is not immediately clear from your screenshot.

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

    Re: 1/2 Days (Access 2K)

    Hans
    The underlying query for the report is "qryAttPeriod" :

    SELECT tblAttPeriod.PeriodID, tblAttPeriod.UnitID, tblAttPeriod.FromDate, tblAttPeriod.ThruDate, tblAttPeriod.ColorKey, tblAttPeriod.WorkGroup, tblAttPeriod.Other
    FROM tblAttPeriod
    WHERE (((tblAttPeriod.FromDate)<=[Forms]![frmAttendance]![txtDateThru]) AND ((tblAttPeriod.ThruDate)>=[Forms]![frmAttendance]![txtDateFrom]));

    The field in the report which calculates the total is "txtSum" with a control source of :

    =WeekdaysMinusHolidays([FromDate],[ThruDate])

    Does this help any, or can you suggest another method ?

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

    Re: 1/2 Days (Access 2K)

    And which of the fields contains 1/2 if the employee has only 1/2 day off?

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

    Re: 1/2 Days (Access 2K)

    Hans

    The report field is called "Other"
    And the same in the qryAttPeriod's underlying table.

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

    Re: 1/2 Days (Access 2K)

    Not sure if this will work, but try
    <code>
    =WeekdaysMinusHolidays([FromDate],[ThruDate])-IIf(InStr([Other],"1/2")>0,0.5,0)
    </code>
    This will look at Other, and subtract 0.5 if it contains the string 1/2. Check carefully!

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

    Re: 1/2 Days (Access 2K)

    Checked, Checked , and Double checked, Perfect. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Thanks Hans.

Posting Permissions

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