Access SQL Null Queries?

Does anybody know if we can get a query to give a single, zero result if it's emtpy? If a query yields no results, basically nothing appears in the result table. I have a subreport connected to this query and the subreport appears as an empty spot on my report. How can we make the query give out a single result although there are no results?

4 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    use the ISNULL function

    SELECT ISNULL(columnname,0)

    FROM tablename

    this will show a 0 when the column returns a null. 0 and null aren't the same so you would be better of using something like:

    SELECT ISNULL(columnname,'NULL')

    FROM tablename

    This will actually show the word null instead of nothing at all.

    • Commenter avatarLogin to reply the answers
  • 1 decade ago

    The way I've solved this issue before when a client wanted to see an empty table instead of no table at all was to UNION ALL my query with a blank record (ie select "", 0, whatever you want as long as the number of fields match) and then I hide the row with the blank record, so it doesn't appear in the table but the table still appears.

    • Commenter avatarLogin to reply the answers
  • 1 decade ago

    You can use "count" if you don't need any of the data in the tables. If your doing some coding that deals with database, you probably may want to program your code to check if the return is null instead.

    • Commenter avatarLogin to reply the answers
  • 1 decade ago

    You should work around the NoData event for the report.

    Read Help about NoData event.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.