Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right
|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

sql