IIf(IsNull)

[Mike Will via AccessMonster.com]

I am having trouble with this line in my sql statement.

IIf(IsNull(DSum("OTHours","tblOvertime","EmpID = " & tblEmployee.EmpID)),0)+
[OTHrsIn] AS OTBal

It puts nothing in the field of those records that should return some number.
So it's basically making them null values. When I was using Nz(DSum
("OTHours","tblOvertime","EmpID = " & tblEmployee.EmpID)),0)+[OTHrsIn] AS
OTBal I had no problems. But my ODBC driver will not recognize the function
Nz. That is why I am using IIf(IsNull).


Help would be appreciated. Thank You.

 

[Douglas J. Steele]

I doubt your ODBC driver can recognize DSum either: as far as I know, that's
specific to Access.

 

[Jeff Boyce]

Mike

Another potential approach would be to first get the Nz(DSum(...)) portion
working, say, as a field in a query. Once that's working, you could create
another query, based on the first, and do your IIF() calculation.

Not very elegant, but...<G>

Regards

Jeff Boyce
Microsoft Office/Access MVP