how to return a blank record if there are no records

[Sudha]

Hi,
I have a query which uses the "Total" in its row. using that i find the sum of cetain fields. now if there are no records to return for the query I would like to get a blank record to be returned. I know when "total" is used it doesn't return blank record. how can I get it done?

Sudha

 

[ajetrumpet]

you can use IIF() statements on every field in the query that you are returning, and in the FALSE section of the statement, use "". Thus, if your "totals" field ends up 0, or blank, or whatever you want that is, make all fields a zero length string.

Example:

SELECT dsum("myfield", "table", "[myfield] = a number") AS totalfield,
   IIF(totalfield = 0, "", table.field1) AS [field1],
      IIF(totalfield = 0 "", table.field2) AS [field2,
         etc, etc...

 

[Sudha]

I am sorry, by "total" i meant the totals toolbar button