If field = 0 then show BLANK field (1 Viewer)

[YNWA]

Hi,

I have a report based on a query. What I want to happen is any fields that return a 0 or £0.00 value, to show as a blank cell rather than the populated 0 version.

Is this possible?

Thanks

 

[JohnLee]

Hi,

In the report properties use the conditional formating option.

Select the text box object that is associated with the field in question and the click on Format/Conditional Formating and then where you see Condition 1 use the options there to change the font colour to white if the value in that field is 0 [zero]/ 0:00.

You may have to play around with it a bit, but that should give you what you want.

Regards

John

 

[Rx_]

Or, add a function into the query. In the query table the field would be something like:
My Final Price: iif([My Price] = 0, Null, [My Price])

This would be OK for smaller reports. If you are pulling large data sets from a linked SQL Server, the MSAccess iif function in an Access SQL Statement can not be converted into TSQL and it will not run efficiently.

Another way would be to pull the data into a temp table - then top it off by running a update query against the temp table.

If the data only goes to a report, the solution above looks pretty good.

 

[YNWA]

Or, add a function into the query. In the query table the field would be something like:
My Final Price: iif([My Price] = 0, Null, [My Price])

This would be OK for smaller reports. If you are pulling large data sets from a linked SQL Server, the MSAccess iif function in an Access SQL Statement can not be converted into TSQL and it will not run efficiently.

Another way would be to pull the data into a temp table - then top it off by running a update query against the temp table.

If the data only goes to a report, the solution above looks pretty good.

Thanks.

So my report runs off a query.

Would you advise your one above or other solution?

This is just for 1 report in an Access database.

In my query would I just add a new column with that criteria in?

 

[Rx_]

My Final Price: iif([My Price] = 0, Null, [My Price])
The field name will show up as My Final Price
The value in the field's columns will will be up to the Immediate If statement
So, you can replace the column that is displaying the zeros.

The setting in the Access report is just as good too.
But, using some additional functionality in a query will be something useful to learn.

Try it out, then be sure to look at the SQL view too.
In the Select statement - it will show the field name with the keyword AS and then the IIF formula.
http://support.microsoft.com/kb/209192

http://www.brainbell.com/tutorials/ms-office/Access_2003/Using_An_IIf_Function.htm

 

[Frothingslosh]

Instead of putting the function into the query, you could also put it into the appropriate field on the report. That way you can run the function without killing the SQL statement's efficiency.