Problem evaluating NULL in an IIF statement (Access)

Question

Item in the recordset rstImportData("Flat Size") is = Null

With that, given the following statement:

IIF(IsNull(rstImportData("Flat Size")), Null, cstr(rstImportData("Flat Size")))
Result: Throws error 94: Invalid use of Null

If I change the statement by removing the type conversion upon a false comparison:

IIF(IsNull(rstImportData("Flat Size")), Null, 0)
Result: Null

It returns Null as it should have the first time. It appears that I cannot do a type conversion in an IIF if the value passed in should ever be null even if it passes an IIF test, it still attempts to evaluate it at both the true and false answer. The only reason I'm using IIF like this is because I have a 25 line comparison to compare data from an Import against a matching record in a database to see if I need to append the prior to history.

Any thoughts? The way data is imported there will be null dates and where the spreadsheet import is in a string format I must convert either side to the other to compare the values properly but if either side is null this exception occurs :(

EDIT Example of why I was using IIF (and considering using a universal function)

If master("one") <> import("one") Or _
   master("two") <> import("two") Or _
   master("date") <> import("date") Or _  //import("date") comes from a spreadsheet, it comes in as string, CAN be a null value
   master("qty") <> import("qty") Or _    //import("qty") comes from spreadsheet, comes in as a string can CAN be null
   master("etc") <> import("etc") Then

....stuff....

End If

This code expands for roughly 20 columns to compare in the database. I would prefer to check as part of the statement. I can think of a bunch of solutions but they involve adding much more code. If that is the case power to it, however I'm not one to give in so easily.

Options I see are

• Creating temp vars to do the work prior to comparing and using these new vars instead of the recordset
• Creating an object to pass the record into to preformat and work with, though extra work would provide this functionality to each import type since there are different files with similar fields

I'm here for ideas, and I'm open to any interesting pieces that can be thrown my way as I get to decide how to do it I'm looking for the most reusable approach.

Answer 1

The behavior you described is the standard way IIf operates under VBA. This is part of what Access 2003 Help says about it:

"IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True."

However, if you use an IIf statement in a query, evaluation short circuits after truepart when expr is True --- falsepart is not evaluated in that case. Unfortunately this information is not useful for you ... unless you can incorporate a query into your comparison.

I don't know of any other way to avoid your error with IIf. I would try appending the Excel data into a table whose structure matches that of the table you will compare against, thereby eliminating the need to do a string conversion at the same time you do the comparison.