Help! I can't count time!

[MRSPK]

Hello,


I have been trying to get 'TotalMins' field to calculate to total minutes between 2 time fields 'StartTime' and 'FinishTime'. All the update queries i make only seem to adjust the underlying date. I have been advised that i will have to have both the date and times in the field,but this would reduce the simplicity of the form, i am using short date as the field settings.

I have also been told that as the finish time is occasionally past midnight ie. 0030 to a 2300 start time that this will also be a problem?

Any help would be appreciated!

 

[gromit]

Hi -

Update queries by their nature change records. If you just what to calculate a value, you can use a calculated expression in an regular (Select) query.

If you subtract two time variables, the result is the difference between the two time, but the result is in days. So you would need to convert days (24 hours) to minutes. Therefore multiple by 24 x 60 = 1440.

Don't confuse the format (how the data is displayed) with the underlying data type (how the data is stored). A Date/Time variable includes day and time information.

Time values that are past midnight may cause you problems. If you enter the full date time value, then Access with calculate the difference correctly. If you just enter 0300 and 2300, Access will assume that they are on the same day and then the difference will not be correct.

Hope that helps.

- gromit

 

[EMP]

....... the finish time is occasionally past midnight ie. 0030 to a 2300 start time .......

You can add one day to [FinishTime] in the calculation when [FinishTime]<[StartTime]. e.g. if seconds are not recorded in the fields, you can use an expression like this:-

TotalMins: CInt((IIf([FinishTime]<[StartTime], [FinishTime]+1,[FinishTime]) - [StartTime])*24*60)


If seconds are recorded in the fields, you can round the minutes to 2 places of decimal like this:-

TotalMins: Round((IIf([FinishTime]<[StartTime], [FinishTime]+1, [FinishTime]) - [StartTime])*24*60, 2)

^

 

[MRSPK]

Thankyou EMP & gromit,

Learning heaps here,

Another question. If the peak times of operation are 0700 and 1000. Is it possible for access to count how many minutes was covered by the start and finish times?
i.e values are StartTime - 0500
Finishtime - 0900
Can access calculate that PeakMins = 120 mins?