CODEWITHSUNDEEP
sqlms-accessms-access-2013
Adarsh Madrecha
Adarsh Madrecha

Reputation: 7906

Error Handling in Queries of Access #Error

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.

Upvotes: 4

Views: 9780

Answers (1)

Newd
Newd

Reputation: 2185

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

Upvotes: 1

Related Questions