MS Access : Why is IsNull function returning nothing?-CodePudding

I've written a query that attempts to return a record from a table. It will either return 1 record or nothing.

I've then written a second query and am trying to check whether the first query returned a record or not.

In order to do this I'm trying to use the IsNull function on the primary key of the record in the first query.

I would expect that when the query 1 returns a record, query 2 will return false and when query 1 returns nothing, query 2 will return True.

Instead, when no record is returned by query 1, query 2 returns nothing, just a blank field - why is it not returning True? This is the SQL for query 2:

SELECT IsNull([query 1].fieldA) AS NullorNot
FROM [query 1];

CodePudding user response：

NULL is an undefined column value WITHIN A RECORD. It is still a column value. A query not returning anything has no columns, no values, so has nothing to do with NULL.

I don't think that your on the right track and should rethink your solution (like just testing for the number of records in whatever code you are using the query), but if this is actually what you need, it is this:

SELECT NOT EXISTS(SELECT * FROM [query 1]) AS NullorNot;

SELECT NOT EXISTS([the full SELECT sql of query 1]) AS NullorNot;