Calculating Time with Date - Microsoft Access Time Fields Might Not Work the Way You Expect Them To. QQ #74


	Back to Quick Queries #74 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Calculating Time with Date

Darrin Harris

4 months ago

Using date hh:nn:ss dosent work properly for example.

hh:nn:ss has a maximum of 23:59 hours works fine for music that's less than that.

Say if you have lots of music what would be the total sum? would it be restricted to 23:59?
What happens when you have to sum a total of 134:21:28 hours of music?

Or am i just not doing it right?

Thank you all

	Donald Blackwell @Reply 4 months ago
I think in that scenario, you'd definitely need to apply techniques Richard showed in the Time as a Duration video where you break hours, minutes, and seconds up so that you can properly calculate the total

Darrin Harris OP @Reply

4 months ago

Thankyou Donald I just finished my music database inspired by the Toine's post

This is how i would do it, witch is a bit like Time as Duration video?

Table
Duration = ShortText

Query
Duration = 0:02:43
Hours: Hour([Duration]) = 0
Minutes: Minute([Duration]) = 2
Seconds: Second([Duration]) = 43
TotalSeconds: ([Hours]*3600)+([Minutes]*60)+[Seconds] = 163

Form
TextField
=sum(TotalSeconds)

Total Length or Duration
Control source
=IIf(IsNull([SumTotalSeconds]),"",IIf(Int([SumTotalSeconds]/3600)=0,"",Int([SumTotalSeconds]/3600) & ":") & IIf(Int(([SumTotalSeconds] Mod 3600)/60)<10,"0" & Int(([SumTotalSeconds] Mod 3600)/60),Int(([SumTotalSeconds] Mod 3600)/60)) & ":" & IIf(Int([SumTotalSeconds] Mod 60)<10,"0" & Int([SumTotalSeconds] Mod 60),Int([SumTotalSeconds] Mod 60)))

	Darrin Harris OP @Reply 4 months ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Quick Queries #74.

Next Unseen

New Feature: Comment Live View