I have a query which uses both ISNULL and CASE to compute values for a couple of dynamic columns. Those two are forcing the result set to have at least one record with: NULL, NULL, NULL, ComputedValue1, ComputedValue2, NULL, NULL and so on...

I have applied duct tape in the form of a quick DELETE WHERE (id) IS NULL to cover up the problem.

Can anyone suggest a method to be able to use ISNULL / CASE and not have doing so force a phony result set record?

I suppose something along the lines of checking the one main table in the query, if that has matching records then run the real query, else NULL.

Does anyone have a sample query which does that? TIA!

I worked up a solution as follows... add this to the line BEFORE the SELECT statement starts...

IF (SELECT DISTINCT CASE WHEN [p].[id] IS NULL THEN 0 ELSE 1 END FROM [dbo].[products] AS [prod] LEFT JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid] LEFT JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id] WHERE [prod].[id] = 1) = 1
SELECT ...

This creative solution ends up raising a VBA error when I plug the query into Access, as follows:

Date: 20120104 Time: 08:30:30 UserID: c_mlueck
AppErrorMsg: Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl()
Error Source: DAO.QueryDef
Error Number: 3325
Error Description: Pass-through query with ReturnsRecords property set to True did not return any records.
MessageText: Error not found.Since the IF conditional is skipping running the SELECT (when matching records are not found), will I need to special handle that error condition / throwing the error away since it is not an error I want to handle?

If so, what is the correct way to ignore a DB error in Access?

I am handing it this way... modeling after another spot I recalled was specially coded to soft handle anticipated errors:

Err_dbutils_RefreshLocalTmpTbl:
'OK to expect 3325 type errors since an IF might prevent the SELECT from executing
If Err.Number = 3325 Then
dbutils_RefreshLocalTmpTbl = True
Else
'further error handling here for other error types
Call errorhandler_MsgBox("Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl()")
dbutils_RefreshLocalTmpTbl = False
End If
Resume Exit_dbutils_RefreshLocalTmpTbl
Hopefully THIS will finally be the last of "ripple effect errors" from tossing a design change in the pond of this project. ;)

I worked up a solution as follows... add this to the line BEFORE the SELECT statement starts...

IF (SELECT DISTINCT CASE WHEN [p].[id] IS NULL THEN 0 ELSE 1 END FROM [dbo].[products] AS [prod] LEFT JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid] LEFT JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id] WHERE [prod].[id] = 1) = 1
SELECT ...

If I may make an observation:

Your Query uses a LEFT JOIN, which, by definition will return records with some NULL Values where there are values in the Table on the Left that are not in the Table on the Right. Since these seem to be the records that you do not want, is it possible that you could use an INNER JOIN Instead? By definition, an INNER JOIN would not return those records.

If I may make an observation:

Your Query uses a LEFT JOIN, which, by definition will return records with some NULL Values where there are values in the Table on the Left that are not in the Table on the Right. Since these seem to be the records that you do not want, is it possible that you could use an INNER JOIN Instead?

A good reminder... hhhmmm... I will consider converting the few JOIN statements that relate the required records back to INNER type.

Why oh why was it that I had to switch to LEFT JOIN a long time ago... hhhmmm... (shrug)

I think that was back when I was relying on always getting fields from the LEFT tables. Now my code uses global DB objects to look up values from the LEFT tables... so perhaps I would be fine converting back to INNER JOIN now. I will give it a try. Thanks!