I have used IIF(expression , truePart , falsePart) in my sql query in ms-access-2010. I had also found another operator NZ(expression,falsePart).
I want to know which operator is faster in terms of time and space complexity and how ?
Example If I want to fetch records from a table having 10k records . which from the above operator is better to use?
Execution of each will be near identical except in extraordinarily large iterations.
In queries - there are a couple of issues I think that stand out. For me those are, the returned data type and the native status of the function.
Immediate If does preserve the data type. Not only that - you can use it to coerce the data type. If you want an Integer back or a Date then you can pass that as your returning parameter. Nz gives you the variant (Text/String) that it wants to give back.
The other issue I mentioned is availability. The Immediate If is implemented by Jet's expression service. The full Access VBA library doesn't need to be loaded to expose it.
In other words - if you create a query such as
SELECT * FROM TableName WHERE IIF(FieldName Is Null, 0, FieldName) = 0
then you can execute that query from code libraries external to Access (not requiring an Access connection).
Jet will evaluate the function. (Not that this is a particularly good query - its actually terrible using either function).
The equivalent
SELECT * FROM TableName WHERE Nz(FieldName, 0) = 0
relies on the fact that Nz is a member of the Access Application object model. It absolutely requires that it is Access which executes the query. Not necessarily a common problem, but a consideration.