On the startup form of the attached mdb, select S.Andrea in the option group, then click the Print button then the Daily Report button, select 28 February 2010 on the calendar and click the Report Preview button. In the report that pops up, the text box labelled "hours elapsed between two readings" gives 23.66.. as a result but by looking at the text box control source and separating the formula in three chuncks, I get 24+0-8 so the result should be 16, how come it isn't?
Here are the three chunks:
DateDiff("h",DLookUp("data","dbo_c_LetturaGiornalieraSTD","Data=#" & Format(DMax("data","dbo_c_LetturaGiornalieraSTD","[Data]<#" & Format([Giorno],"mm/dd/yyyy") & "#"),"mm/dd/yyyy") & "# and IdImpianto = 15 and gruppo = 1"),[giorno]) = 24
TimeSerial(0,[ORALettura],0) = 0
TimeSerial(0,DLookUp("oralettura","dbo_c_LetturaGiornalieraSTD","Data=#" & Format(DMax("Data","dbo_c_LetturaGiornalieraSTD","[Data]<#" & Format([Giorno],"mm\/dd\/yyyy") & "#"),"mm\/dd\/yyyy") & "# and IdImpianto = 15 and gruppo = 1"),0) = 8
Time Difference Formula Doesn't Work Properly
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Time Difference Formula Doesn't Work Properly
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time Difference Formula Doesn't Work Properly
DateDiff("h", ...) returns a number of hours.
TimeSerial returns a date/time value, with 1 day (24 hours) as unit. So TimeSerial(8,0,0) = 8/24 = 0.333...
24 + 0 - 0.333... = 23.666...
If you want everything in hours, you must multiply the TimeSerial values with 24 (the number of hours in a day):
=DateDiff("h",DLookUp("data","dbo_c_LetturaGiornalieraSTD","Data=#" & Format(DMax("data","dbo_c_LetturaGiornalieraSTD","[Data]<#" & Format([Giorno],"mm/dd/yyyy") & "#"),"mm/dd/yyyy") & "# and IdImpianto = 15 and gruppo = 1"),[giorno])+24*(TimeSerial(0,[ORALettura],0)-TimeSerial(0,DLookUp("oralettura","dbo_c_LetturaGiornalieraSTD","Data=#" & Format(DMax("Data","dbo_c_LetturaGiornalieraSTD","[Data]<#" & Format([Giorno],"mm\/dd\/yyyy") & "#"),"mm\/dd\/yyyy") & "# and IdImpianto = 15 and gruppo = 1"),0))
TimeSerial returns a date/time value, with 1 day (24 hours) as unit. So TimeSerial(8,0,0) = 8/24 = 0.333...
24 + 0 - 0.333... = 23.666...
If you want everything in hours, you must multiply the TimeSerial values with 24 (the number of hours in a day):
=DateDiff("h",DLookUp("data","dbo_c_LetturaGiornalieraSTD","Data=#" & Format(DMax("data","dbo_c_LetturaGiornalieraSTD","[Data]<#" & Format([Giorno],"mm/dd/yyyy") & "#"),"mm/dd/yyyy") & "# and IdImpianto = 15 and gruppo = 1"),[giorno])+24*(TimeSerial(0,[ORALettura],0)-TimeSerial(0,DLookUp("oralettura","dbo_c_LetturaGiornalieraSTD","Data=#" & Format(DMax("Data","dbo_c_LetturaGiornalieraSTD","[Data]<#" & Format([Giorno],"mm\/dd\/yyyy") & "#"),"mm\/dd\/yyyy") & "# and IdImpianto = 15 and gruppo = 1"),0))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Time Difference Formula Doesn't Work Properly
Fantastic :-) Thank you Hans