Problem evaluating NULL in an IIF statement (Access)
- by Mohgeroth
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 :(
