Error in Time Calculation

[mtagliaferri]

Hi all, I have a problem which I cannot understand why it comes up sometimes only!!
On "frmTrip" I have a field named TAFB Calc, this field should return time difference in hours and minutes between Duty Date and Check In field and Duty End and Chck Out fields. For some funny reason some records return a value with the minutes in negative, for example instead of displaying 47h and 55 min it displays 48h and -5 min
This can be viewed by click on Control Panel->Scheduler->View/Edit Trip->View/Edit Trips All and double clicking on the date 10/01/2008.
I have created various records and sometimes it gives me the correct result and some times not
Can I get some help?
Thanks

 

[missinglinq]

Which of your 35 or 40 forms holds the code for your time calculation?

 

[namliam]

This is because Datediff is inacurate... when you get a datediff "H" it only looks at the hours! which means...
i.e.
Datediff("H", #10-jan-2008 10:59#,#10-jan-2008 11:01#)
Returns: 1
While it is actually 0

And
Datediff("n", #10-jan-2008 10:59#,#10-jan-2008 11:01#)
Returns the proper 2 tho...

Workaround is
Hours: int(Datediff("n", #10-jan-2008 10:59#,#10-jan-2008 11:01#)/60)
Which will return the proper 0 in this case and the proper 47 in your case.
If the hour is right, the -5 will "automagicaly" refert back to 55

 

[missinglinq]

Mailman, exactly how does the code you posted "automagicaly" return anything for the minutes?

Hours = Datediff("n", #10-jan-2008 10:59#,#10-jan-2008 11:01#)\60

Minutes = Datediff("n", #10-jan-2008 10:59#,#10-jan-2008 11:01#) mod 60

Notice the back slash in the Hours forumla.

 

[namliam]

Linq,

Not my code perse... But the way that it is allready made in his forms...

What about the backslash tho?? Slash is the devisor sign not backslash... Not even sure it works... but I presume it does.


Note tho that you are 'missing' the int() for the hours, which is IMHO pretty important if you dont want to rely on automatic roundings truncs by access.

 

[mtagliaferri]

Thanks to you all, I have used Missinglink tip and I have solved my big issue!!
I need now to make it more perfect and I hope you gurus can help me. The correct hours and minutes will be used to calculated a pay rate which basically the minutes are rounded up to the following quarter of an hour, example: 15h 10m = 15h 15m, 15h 20m = 15h 30m, 15h 32m = 15h 45m and finally 15h 47m = 16h 00m, this value should be returned on a field “Tot H”. To make it more difficult and precise 15m represents ¼ of an hour which means that my pay rate has to be multiplied by the number of hours and 25, example: 15h 15m = x 15,25, 15h 30m = x 15,50, 15h 45m = x 15,75

 

[missinglinq]

What about the backslash tho?? Slash is the devisor sign not backslash... Not even sure it works... but I presume it does.

Note tho that you are 'missing' the int() for the hours, which is IMHO pretty important

Most people don't know about the back slash, which is why I always point it out. People were constantly telling me I made a mistake and (after correcting my 'mistake' by reversing the slash) my code didn't work because it returned more than the Integer that was wanted. The back slash is a divisor that only returns the Integer portion of the division operation! You don't need to wrap it in the Int() function!

10/3 = 3.3333333333333333333333333333333333333333333333
10\3 = 3

Pretty cool, eh?

 

[raskew]

Hi -

Give this a try:

Public Function NewNear(intMin As Integer) As Single
'*******************************************
'Name:      NewNear (Function)
'Purpose:   Input number of minutes, output
'           hours minutes as a decimal to the
'           nearest quarter hour (rounding up)
'Author:    raskew
'Inputs:    1)? NewNear(1234)
'           2)? NewNear(15)
'           3)? NewNear(16)
'Output:    1) 20.75
'           2) 0.25
'           3) 0.5
'*******************************************

Dim intHold As Integer, minHold As Integer
intHold = intMin
minHold = intMin Mod 60
intHold = intHold \ 60
NewNear = Nz(intHold + ((15 * (minHold \ 15) + IIf(minHold Mod 15 > 0, 15, 0)) / 60), 0)
End Function

HTH - Bob

 

[mtagliaferri]

Bob, can you give me more step by step information, as I would NOT call my self a access developer ....all I have done is with hints of you guys
Where should I place this code, and more exactly does it calculate it from my "TAFB Calulated" field?
Thanks again!!
Marco

 

[raskew]

Hi -

For some reason, I can't get your application to open-up. Have downloaded it twice, but when I attempt to open it I'm left with a blank A2003 screen.

Well anyway, copy/paste the function to a standard module. What it does is take a number of minutes and returns a decimal representing hours and quarter-hours, with the quarter hours always rounded-up to the next quarter (0.25, 0.50, 0.75, 0.0) if the number of minutes exceeds 0.

Examples:

? newNear(Datediff("n", #10-jan-2008 10:59#,#10-jan-2008 14:01#))
3.25 (i.e. 3 hours 15 minutes)

in reality, the datediff() returns:
? Datediff("n", #10-jan-2008 10:59#,#10-jan-2008 14:01#)
182 (i.e. 3 hours 2 minutes)

Is there a problem with your field? If not, NewNear(), when applied to your fields output, would perform exactly as described above.

If there's still question, would you please post some examples.

Thanks - Bob

 

[mtagliaferri]

Thanks Bob, will try!
To open the Example DB I have posted you need to keep pressed the shift key and open the file!
Marco

 

[raskew]

Marco -

Finally got it open.

Copy/paste this to a new query:

SELECT
    tblTrip.IDTrip
  , tblTrip.TripDate
  , tblTrip.CheckIn
  , tblTrip.TripDateEnd
  , tblTrip.CheckOut
  , [TripDate]+[CheckIn] AS dtStart
  , [TripDateEnd]+[CheckOut] AS dtEnd
  , DateDiff("n",[dtStart],[dtEnd]) AS BobTotMins
  , [BobTotMins]\60 AS BobTotHrs
  , [BobTotMins] Mod 60 AS BobMinsLeft
  , NewNear([BobTotMins]) AS BobNewNear
FROM
   tblTrip;

You'll note that in BobNewNear I've applied the NewNear() function to BobTotMins. It works as advertised.

Guess you've solved the error problem, since I couldn't turn up any calculation problems.

Only thing I'd suggest is that when combining a date value with a time value, rather than [TripDateEnd] & " " & [CheckOut], add them together, i.e. [TripDateEnd]+[CheckOut]. I'm suprised your method seems to be working. Here's an example why from the immediate window:


a = now()
? a
8/9/2008 10:55:42 PM
b = datevalue(a)
c = timevalue(a)
? b
8/9/2008
? cdbl(b) 'this is how dates are store by Access
39669
? c
10:55:42 PM
? cdbl(c) 'this is how times are store by Access
0.955347222222222

d = b & " " & c 'this is how you've been joining them
? d
8/9/2008 10:55:42 PM
? cdbl(d)
returns Type Mismatch (because what you have, is in fact a string)
d = b + c Adding them together creates a double, not a string)
? d
8/9/2008 10:55:42 PM
? cdbl(d)
39669.9553472222 this is how Access stores date/times

Why that hasn't created problems, I'm not sure. But, to be safe I'd recommend adding them rather than joining with a " ". Ideally, you'd save time and trouble if you created a combined date/time field and use that rather than your separate date and time fields.

You might want to take a look at this MSKB article which goes into detail re Access dates & times: http://support.microsoft.com/kb/q130514/

HTH - Bob

 

[mtagliaferri]

Hi Bob, thanks again for your tips, have been busy and just seen your post.
I have done as you told me, but I have an error when launching the query (query1)
I need to return the result on my form and also as a written value on my table.
I have attache the file with the changes (shift + click to open)
Marco

 

[raskew]

Hi -

You can't name your module with the same name as a procedure. It results in what you encountered.

Rename the module 'basNewNear' and your query will process.

Bob

 

[mtagliaferri]

Hi,

I have made the corrections and I used the criteria to place on my form "frmTrip" the text box to show the Hours and Mins rounded up.
I need to transfer this calculated value as a permanent value on my "tblTrip" this would make it easyer when calculating the total grouped by PSR in "qryDutyPaySimulation". I have placed and after update on the "BobNewNear" text box but it seems not to work.
How can I solve this issue?

Thanks again for your great help

Marco

 

[raskew]

I'd suggest that you expand Query1 to include those items you'll need in qryDutyPaySimulation, then base qryDutyPaySimulation on Query1.

You're not going to return anything in BobNewNear until you've computed the number of minutes, as we've done in Query1. Likewise, you can't compute number of minutes until you've combined startdate+starttime & enddate+endtime, again included in Query1.

Bob

 

[mtagliaferri]

I thought that was the best solution as I only need to see the total as I launch the qry!
I have added the fields that I need into my qry1, but I cannot group by PSR as in qryDutyPaySimulation!
In qryDutyPaySimulation I get the totals per month of: TOT TRIPS, TOT DAYS, TOT FLYING TIME, TOT ADDITIONAL ALLOWANCES .... all grouped by the same PSR value, of course in the new Query1 I would need to make the total of BOBNEWNEAR so I can moltiply it by DutyPayRate for the total payment due for the month.

I really apprecite your ongoing patience you got with me

Rgds
Marco

 

[raskew]

Marco -

Would you please define the mystic terms, e.g. PSR. I'm getting wrapped up trying to figure what it is we're computing, grouping by, etc..

Thanks - Bob

 

[mtagliaferri]

Hi Bob

PSR is a value in tblTrip which is obtained when creating a new record using the CONTROL PANEL form by clicking on SCHEDULER-> ADD TRIP/FLIGHT the PSR field if generated when the user input on the form "frmTrip" the Duty Date, N Of Days, Check In and Check Out, when these data are all inserted in the form you will noticed that a value in the form of YYYY MM will appear in the text box named PSR and also will return this value into tblTrip, when running the qryDutyPaySimulation all data are gouped by the same YYYY MM value.
Hope I gave you detailed information.

 

[dayna]

Most people don't know about the back slash, which is why I always point it out. People were constantly telling me I made a mistake and (after correcting my 'mistake' by reversing the slash) my code didn't work because it returned more than the Integer that was wanted. The back slash is a divisor that only returns the Integer portion of the division operation! You don't need to wrap it in the Int() function!

10/3 = 3.3333333333333333333333333333333333333333333333
10\3 = 3

Pretty cool, eh?

Thanks, Misslinq! I had a similar issue...though my calculations did not involve division, I kepts getting ######## in fields where the result should have been 8. The calculations were working in every other instance, and I couldn't (and still can't) figure out why.

But, I used your backslash trick to "divide" the result of the calculated field by 1, and it works a charm!