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
    Best 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.

  • 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.

  • 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.

  • 1 decade ago

    You should work around the NoData event for the report.

    Read Help about NoData event.

Still have questions? Get your answers by asking now.