Access 2013 Calculated Field Help (1 Viewer)

[erummel]

Hi,

I'm new to Access (like thousands of others here :banghead and am having an issue with Access not liking to display elapsed time over 24 hours correctly.

My original expression for this field was [Outage End] - [Outage Start]. I kept the result type as Date/Time but used a custom Format: dd:hh:nn.

When I was going over some of the latest data entered in the database today, I noticed that any calculation that had a result of over 24 hours was wrong. I've tried a myriad of supposed ways found on the internet to fix this, but either the instructions on how to use the code are vague and unclear or it just doesn't work.

A few examples:

This works-

Outage Start- 05/08/2014 18:07
Outage End- 05/08/2014 18:30
Elapsed- 00:23


This does not:

Outage Start- 05/08/2014 01:08
Outage End- 05/12/2014 15:07
Elapsed- 13:59 (should read 04:13:59)

Any detailed help on how to make this work right would be very very much appreciated.

 

[CJ_London]

Your construct is based on a wrong assumption - that d can be any number when in fact it is limited to 1 -30 so if you have an outage greater than 30 days you would get an error or a least a wrong result. Similarly there is no 0 so I would have expected your first example to be wrong. In short, the format function for dates is for dates, not elapsed time.

Try this construct instead

=Int([range to]-[range from]) & " " & Format([range to]-[range from],"Short Time")

you can still use the format function for the time part since this is only looking at the decimal part of the date/time value and can only be between 0 and 11:59:59

 

[Ari]

Hi

Another way is to convert to minutes. Example:

datediff("n",#05/08/2014 01:08#,#05/12/2014 15:07#) :::> 9599 minutes

Make the necessary calculations on the basis of minutes which is pretty easy, because it is integer type

In the end, then just convert the minutes to the format "hh:mm"

hour :::> 6599\60
Minutes :::> 6599 - ((6599\60)*60)

Result ::::> 109:59

Or (dd:hh:mm)

day: 6599\1440
hour: (6599-(day*1440))\60
Minutes: 6599 - (day*1440) - (hour*60)

Result :::> 04:13:59

 

[erummel]

Try this construct instead

=Int([range to]-[range from]) & " " & Format([range to]-[range from],"Short Time")

I know this may sound silly, but I don't know where to use this construct. I have tried pasting this into the Expression Builder, replacing [range to] and [range from] with my actual field names, but I get an error returned saying that the above expression cannot be used in a calculated column.

What am I doing wrong?

 

[erummel]

Does anyone have any suggestions on where I actually put the string referenced above?

 

[CJ_London]

I get an error returned saying that the above expression cannot be used in a calculated column

suggest you post your complete sql

Does anyone have any suggestions on where I actually put the string referenced above?

it calculates your elapsed time so would something like

Select Int([range to]-[range from]) & " " & Format([range to]-[range from],"Short Time") as ElapsedTime From someTable