Negative Time Values (Access97)

Topic

New Reply

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?

Reply | Quote

Replies

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:

?Cdbl(#10/16/01 8:00# – #10/17/01 8:00#)

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:

iif(dteGain<0,"-","")&format(abs(dteGain),"hh:mm")

(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.

Reply | Quote

Thanks for the response,
I’ll need to dig out my Access For Dummies book and give it a go . Incidently i believe you put “[hh]:mm” to prevent the 24 hour limit.
Ewan

Reply | Quote