Returning Null with WHERE

I have a very basic query for Access using SQL, but I want to get it to bring back Null if nothing is returned.

For example if I state

WHERE Cust_ID = "win"

If there are no "win" fo开发者_StackOverflowr the query to bring back Null.

I had thought I could use ISNULL but have not managed to get this to work.

Thanks in advance Zane

It brings back an empty set, not NULL. How you determine whether the set is empty or populated will depend on what client software you are using to interface with the database. You can count the rows in ADO.NET to see if the count is zero.

Consider using DLookup() instead of a recordset. DLookup() will return a Null in circumstances where a recordset would simply be empty:

  DLookup("LastName","tblInventory","[InventoryID]=-1")

Since my tblInventory has no records with InventoryID -1, it returns Null.

The same SQL statement:

  SELECT LastName FROM tblInventory WHERE InventoryID=-1;

...returns an empty recordset.

So, it seems to me that DLookup() does what you want. It also doesn't require any code and can be used as the ControlSource of a control on a form or report.

To get a null back if a where condition fails will require you to either do a case statement around a column definition or a left outer join instead of the where clause.

iif(Cust_id = "win", "win", null) 

or

LEFT OUTER JOIN tablename on
tablename.cust_id = "win"

should work - but I don't have Access available to check.

I'm not sure if this works in Access, but if you don't mind having always a NULL in your results, you can try appending "UNION ALL SELECT NULL" to your statement.

A query always returns a recordset, which roughly corresponds to a list of records. If no records match your condition, an empty list is returned, which is not the same as NULL.

Example:

Set rset = ...   ' Execute your query here '
If rset.EOF Then
    ... ' No records found '
Else
    ... ' Loop through the rset with rset.MoveNext until rset.EOF is true '
End If

If no records match the condition, no records will be returned. You could trick the system into doing what you want by JOINing in a one-record table. But why do you want this odd behavior rather than the regular (and, it seems to me, more sensible) lack of any results when nothing matches.