Microsoft Access query seen as a Function or View by Visual Studio 2005

Question

I have a Microsoft Access database query that I'm trying to import into a Visual Studio 2005 dataset.

When the query is formed using an NZ() function like this:

SELECT NZ(tblComponentSpecs.nPurchaseCostQuantity, 0) AS Quantity
FROM tblComponentSpecs;

it appears under the Functions list in the Data Connection.

However, when the query is formed using an IIF() function like this:

SELECT IIF(tblComponentSpecs.nPurchaseCostQuantity Is Null, 0, nPurchaseCostQuantity) AS Quantity
FROM tblComponentSpecs;

it appears under the Views list.

Can anyone please explain why?

Answer 1

Probably this is because Nz() is a VBA function, whereas IIF is part of Jet SQL. (Yes, there also is a function named Iif() in VBA. Further info is on Allen Browne's web site.)

I guess that IIF is translated to CASE WHEN and then makes a valid view, and Nz() is not translated.