Detecting Not-A-Number (NAN) or +-infinity in Acce

Question

Oracle provides functions to test whether a stored value is NaN or +-infinity. Is there something similar that can be used in Access and Sql Server?

Answer 1

Yes, Access can detect "NaN" and "+/-Infinity", at least for values that reside in Double columns of a native Access table:

You can even have Access perform queries like the following, which will return all rows that contain a "NaN" (positive or negative) in the Double column:

SELECT * FROM Table1 WHERE Right(CStr([DoubleField]), 3) = 'NAN';

However, support for those special values could very well be incomplete. For example, I'm not aware of a way to insert or update those values (e.g., CDbl("1.#INF") does not work.)