4

I am working on a Attendance table in Access, where I have InTime and OutTime. These fields are of Date/Time Field. Some records contains only Time like 11:40:00, some contain Date as well as time like 21-07-2015 11:45:00. Hence have used the below code for getting hours worked.

HrsPresent: Round(DateDiff("n",TimeValue(TimeSerial(Hour([TimeIn]),Minute([TimeIn]),Second([TimeIn]))),TimeValue(TimeSerial(Hour([TimeOut]),Minute([TimeOut]),Second([TimeOut]))))/60,2)

Using this above code, in a Column in making query gives correct Number of hours worked, but if any of the field is blank, i get #error in result.

I have tried using Nz , IsError, IsNumeric but all in Vain.

  • What is it that, I am doing wrong?
  • Is other way of getting hours worked?
Adarsh Madrecha
  • 6,364
  • 11
  • 69
  • 117

1 Answers1

1

You basically just need to make sure that both of the needed fields aren't blank before performing your calculation. You could do this using two IIF statements. If one field is blank then you simply assign a default value or handle it how you want.

In my example the default is zero, I have to warn you though this was done free hand and I am not super confident that my brackets line up properly. Also I am sorry that it is all on one line, I couldn't think of a logical way to break it down. 

HrsPresent: IIF(Nz([TimeIn],"") = "", 0, IIF(Nz([TimeOut],"") = "", 0, Round(DateDiff("n",TimeValue(TimeSerial(Hour([TimeIn]),Minute([TimeIn]),Second([TimeIn]))),TimeValue(TimeSerial(Hour([TimeOut]),Minute([TimeOut]),Second([TimeOut]))))/60,2)))

For more information on IIF statements you can use visit here: http://www.techonthenet.com/access/functions/advanced/iif.php

Newd
  • 2,174
  • 2
  • 17
  • 31