Error Handling in Queries of Access #Error

Question

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?

Newd · Accepted Answer · 2015-07-02 17:26:50Z

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

edited Jul 2, 2015 at 17:26

answered Jul 2, 2015 at 17:21

Newd

2,1832 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adarsh Madrecha Over a year ago

Thanks so much, Now understood, where to add Nz. I was adding to whole function.

Collectives™ on Stack Overflow

Error Handling in Queries of Access #Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related