I'm using MS ACCESS 2021 on a WINDOWS 10 machine for the below conversation:
I've a database where a service is provided by time. So there's a StartTime and an EndTime field and they are in a date / time format and the time looks like HH:MM.
To compute the time spent I use a calculated form of [EndTime]-[StartTime] which produces the TotalTiemSpent in the form of total HH:MM
Then to produce the number of minutes spent on the service I use another calculated formula and multiply [TotalTimeSpent] * 1440 and this creates the value TotalMinutes spent on the service.
Now, I need to convert this to the format of hours so taking [TotalTimeSpent] / 60 produces TotalServiceTime which is number of hours in a decimal format. in the form of H.H Hours and 1/10's of hours
Here's the kickers..
1.
If I specify the result time as "Singe" and the "Decimal PLaces" value as 1, AND the resulting value of [TotalTimeSpent] works out to be some multiple of 1/10 of a hour, (00, 06, 12, 18, 24, 30, 36, 42, 48, 54) the decimal works out to be .0, , .1, .2, .3, .4, .5, .6, .7, .8, or .9
But in all other cases the decimal field is not honored.
Since I don't want to charge for anything between the full decimals, I'd like to round the values from 0.01 - 0.09 to .00, 0.11 - 0.19 to .10, 0.02 - 0.29 to .20 and so on... rather ACCESS produces something like: 1.666667E-02
2.
In Excel I can use a formula like:
where F7 - TimeEnd and E7 is TimeStartCode:=ROUNDDOWN(MROUND(PRODUCT(F7-E7,24),"0:06"),1)
But I can't seem to sort out how to cause this functionality to calculate in ACCESS...
I'd prefer to have this calculated and stored in the database as I suspect when working on millions of records for a report would tend to cause the machine to slow down in either a query or a report when having to calculate each included record (and yes, there are going to be millions - hundreds of millions of records
for each query / report)
Is there a bug in how ACCESS computes the decimal function in the case I'm interested?
Is there a way to cause this to compute the way I'd like it to?
I'm in the throws of learning ACCESS.. so any help / guidance will be greatly appreciated..
Thanks in Advance
---Whack