Calculating Times

[shakermaker]

Help!!!!!

I've set up an expression from 2 fields in a form (Start Time & Finish Time). BY setting the expression it works out the difference and puts it in another field called completion time. Problem is when I look in the table the information in the completion field isn't there. WHY?

PLEASE HELP ME I CANT DO MY QUERY IF IT's NOT THERE

cheers
neil bond

 

[dcx693]

Problem is when I look in the table the information in the completion field isn't there. WHY?

You are calculating a vale on your form and you want it stored in your table? You can get it to save if you create a field in your table to store the value, then set the controlsource of the field doing the calculation on the form equal to the name of the field from the underlying table. Of course then, you'll need some VBA to set the value of the field...but why would you want to?

The question most people here will ask is, "Why do you want to store a calculated value in your table anyway?" And they'd be right. Since you can always calculate the value when you need it, storing calculated values goes against the rules of data normalization meant to ensure optimal database design.

But then you write:

I CANT DO MY QUERY IF IT's NOT THERE

Just do the calculation in your query instead of on the form.

 

[shakermaker]

IM gonna cry

I have just learnt how to do a calculation in a query, BUT i have a problem.

I have 2 fields
Start time says 11.00am
Finish Time says 11.45am
I do the calculation in the query of Finish time - Start Time
BUT the answer is never 45 minutes it is for example 0.0032345.

The Format only allows me to make the query field into a numeric field and not into a date & time

what can i do?

 

[Ajayi32]

If you take the result and multiply it by 1440, you will get the resulting minutes. In this case 45.

 

[dcx693]

Yes, but 0.0032345 * 1440 = 4.65768.

 

[raskew]

There is something wrong with the example or perhaps I just don't understand.

If, from the debug window, you have:

x = #11:45# - #11:00#
? x
0.03125

...not 0.0032345, as was suggested.

And if you then type:

? x * 1440 'representing 24 hours * 60 minutes

the result is
45

The above is all done in 'native' Access code where date/time data types are stored as an (15 position, floating-point double) offset from #12/30/1899#.

Yeah, there are folks on this forum that believe this is the way to go and, I have to agree that it can be done--in some circumstances!

However (comma), it just ignores all of the built-in (and infinitely more useful) date/time functions, e.g. datediff(), timediff(), etc..

Consider the poster's scenario. He/she wanted to know how many minutes there were between a start time of #11:00# and an end time of #11:45#.

He/she has been given some bad advice or has misinterpreted the advice provided and has ended up with a probomatic situation that no-one is going to be able to sort-out!

Why not just:
? timediff(#11:00#, #11:45#, "n")
...and cut-out all the interim crap to return
45

End of rant.

Best wishes - Bob

 

[dcx693]

Using TimeDiff() would be nice....if such a function existed.

 

[raskew]

You're absolutely right! Have used it for so long that I'd convinced myself that it was a native Access function ("...he's a legend in his own mind!") . Code follows:, point still stands:

Function timediff(ByRef starttime As Date, ByRef endtime As Date, Interval As String) As Variant
'*******************************************
'Name:      timediff (Function)
'Purpose:   Return the number of minutes between
'           two times, dates or date/times
'           Calls Timesay() to convert minutes
'           to a string
'Inputs:    (1) ? timediff(#12:31#, #19:10#)
'           (2) ? timediff(#07/04/02#, #07/06/02#)
'           (3) ? timediff(#07/04/02# + #12:31#, #07/06/02# + #19:10#)
'Output:    (1) 0 days 6 hours 39 minutes
'           (2) 2 days 0 hours 0 minutes
'           (3) 2 days 6 hours 39 minutes
'*******************************************

Dim intHold As Long
timediff = DateDiff(Interval, starttime, endtime)

End Function

'*******************************************
Function timesay(pInt As Long) As String
'*******************************************
'Name:      timesay (Function)
'Purpose:   Converts long integer representing
'           number of minutes to a day/hour/minutes
'           string
'Inputs:    ? timesay(795)
'Output:    0 days 13 hours 15 minutes
'*******************************************

Dim intHold As Long
Dim strTime As String

intHold = pInt

strTime = intHold \ 1440 & " days "
intHold = intHold - ((intHold \ 1440) * 1440)
strTime = strTime & intHold \ 60 & " hours "
intHold = intHold - ((intHold \ 60) * 60)
strTime = strTime & intHold Mod 60 & " minutes "
timesay = strTime
End Function

Best wishes and thanks for pointing that out.
Bob

 

[dcx693]

Nice!

 

[Jon K]

My favourite expression:-

Int([EndTime]-[StartTime]) & format([EndTime]-[StartTime], " \d\a\y h \h\r n \m\i\n s \s\e\c")

which will return ? day ? hr ? min ? sec