Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative Time Values (Access97)

    I'm a complete novice with Access so please forgive the stupid question...
    I work a flexitime system where each working day my "flex account" is debited by 8hours 20min. We credit our account by either attending or being entered as on holiday etc. The next day the system reports how much we were + or - the 8 hours 20min. Due to a screw up with the time keeping system our reported overall balance is all over the place so i have cobbled together a system in Excel to track employee flex. As Excel can't handle negative time values this means i have formulae converting between text entries, decimal time and back to text, it isn't pretty.
    My intention is to have a table with the fields:
    EntryID(Autonumbering primary key), EmployeeID, TimeGained
    TimeGained being entered as + or - so many hours and minutes

    I would then have a report show the sum of TimeGained by EmployID.
    Can Access handle negative time values or am I as well stick to Excel?

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Time Values (Access97)

    The date data type is a 64-bit floating point number (same as the 'double' type). When you subtract two dates/times resulting in a negative time you get a negative time. For example, try this in the VBA debug window:
    <font face="Georgia">
    ?Cdbl(#10/16/01 8:00# - #10/17/01 8:00#)
    </font face=georgia>
    You get a result of -1 (i.e., 1 day).

    Retaining this 'double' data type (using either the 'Double' or the 'Date' data type) will let you deal with the math without all the text conversions. When you get to the point you want to display the value, use something like:
    <font face="Georgia">
    iif(dteGain<0,"-","")&format(abs(dteGain),"hh:mm")
    </font face=georgia>
    (I'm not sure how you want to display the gain -- the above will have problems if the abs(dteGain) > 1; i.e., > 24 hours.)

    Hope this helps a little.

  3. #3
    Lounger
    Join Date
    Aug 2001
    Location
    Shropshire, UK
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Time Values (Access97)

    Thanks for the response,
    I'll need to dig out my Access For Dummies book and give it a go <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. Incidently i believe you put "[hh]:mm" to prevent the 24 hour limit.
    Ewan

Posting Permissions

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