Query with No Records

Question

Experts -

I have a query with 1 field that will sometimes return no results, and sometimes will.  When it doesn't return results, rather than showing nothing I need it to return dummy values.  The query is as follows:

SELECT Sub_Category_1
FROM table
WHERE Date_Completed IS NULL 
AND Date_Assigned IS NOT NULL
AND Sub_Category_1 = 'ABC'
GROUP BY Sub_Category_1

I am trying to do this:

SELECT
IIF(COUNT(Sub_Category_1) = 0, 'ABC', Sub_Category_1)
FROM table
WHERE Date_Completed IS NULL
AND Date_Assigned IS NOT NULL
AND Sub_Category_1 = 'ABC'
GROUP BY Sub_Category_1

But don't get any results.  If I remove the GROUP BY, I get the following error:

"You tried to execute a query that does not include the specified expression 'IIF(COUNT(Sub_Category_1) = 0, 'ABC', Sub_Category_1) as part of an aggregate expression"

Any idea what the solution is?

Thanks!

---

Bonediggler

Answer 1

You can use a UNION operation to return a dummy row in the event of the query not returning any rows.  The following is a simple example which will return the first names of any contacts named 'Smith' if at least one exists, or 'No such contact' if none exist.

SELECT FirstName
FROM Contacts
WHERE LastName = "Smith"
UNION
SELECT "No such contact"
FROM Contacts
WHERE NOT EXISTS
   (SELECT *
     FROM Contacts
     WHERE LastName = "Smith");

Note that each part of the UNION operation must return the same number of columns, so if the first part  returns additional columns and you only want to show a dummy value in one column include NULLs in the second part's SELECT clause for the additional columns, e.g.

SELECT FirstName,LastName, DoB
FROM Contacts
WHERE LastName = "Smith"
UNION
SELECT "No such contact", NULL, NULL
FROM Contacts
WHERE NOT EXISTS
   (SELECT *
     FROM Contacts
     WHERE LastName = "Smith");

---

Ken Sheridan, Stafford, England

Answer 2

Hi. First, your original query does not need to have a GROUP BY clause since you're not using it. Second, for what purpose are you using the query? Why do you need to open it to see the data? User interface is better off using just forms and reports. Tables and queries should be hidden from the users.

Answer 3

Hi Bonediggler,

here I would recommend you to check the suggestion given by the Ken Sheridan

I had check and test the suggestion given by him and find that it can solve your issue.

please check it and let us know it worked for you or not.

if it worked then I would recommend you to mark the suggestion of Ken Sheridan as an Answer.

if not then we will try to provide further help to solve your issue.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.