CODEWITHSUNDEEP
sqlms-access
Rick
Rick

Reputation: 2308

Access query with no returned results

I have a query in Access and would like to know if it were possible to use the where not exists clause to display a specific text for each field when there are no returned rows.

Example query: 

Select Field1, Field2, Field3  
From TableA  
Where Field1 = "test";

If there are no returned results I would like the following to return: 

Field1 = "test"  
Field2 = "not provided"  
Field2 = "not provided"

Upvotes: 1

Views: 765

Answers (2)

Fionnuala
Fionnuala

Reputation: 91316

How about:

SELECT Field1, Field2
FROM Table
WHERE ID=3
UNION ALL SELECT DISTINCT "None","None" FROM AnyTableithAtLeastOneRow
WHERE 3 NOT IN (SELECT ID FROM Table)

Upvotes: 6

Alain
Alain

Reputation: 27220

The usual way to do what you're asking is:

Select Field1, isnull(Field2, 'Not Provided'), isnull(Field3, 'Not Provided')

edit whoops, you're using Access, in that case the equivalent function is "nz" (what?! :p)

Select Field1, nz(Field2, 'Not Provided'), nz(Field3, 'Not Provided')

Upvotes: 0

Related Questions