If field = 0 then show BLANK field

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

 

[WaterLover]

I know this post is old but wanted to add my input for anyone who will find it helpful.

The problem with entering "" or Null when the value is 0 is that you can't total these values and you'll get an error if the field's Format is set to a numerical format (i.e., Standard).

And, while conditional formatting (setting the font color to white) works, the problem with using conditional formatting is that if you have a running sum field, it'll display #Name? even if there is a value. The other issue with conditional formatting is that you can't hide any lines / double lines you have above and below total fields.

So, here's how I got it all to work:

In the report's applicable section (Detail, Header, Footer, etc.) put the following vba code in the section's On Format event: [Field to Hide].Visible = [Field to Hide] <> 0. For the lines above and below totals, name each line and enter the following vba code in the On Format event for the report section the total is in: [Field to Hide Total Top Line].Visible = [Field to Hide Total] <> 0.

 

[Pat Hartman]

The format property for numbers has three options. That solves the problem. I don't have the syntax handy but you can look it up. I think Excel uses the same syntax

 

[Galaxiom]

This is what Pat is talking about.
Put this in the Format property of the control or field. You can use the usual formatting instead of the the plain #

#;#;;

They represent the format for positive, negative, zero and text or null respectively
Unlike the clumsy function suggestions above, the control or field remains editable.
Much simpler than ConditionalFormatting.

 

[DickyP]

The format property for numbers has three options. That solves the problem. I don't have the syntax handy but you can look it up. I think Excel uses the same syntax

It has 4 options - positive, negative, zero and null

 

[Pat Hartman]

It has 4 options - positive, negative, zero and null

@Galaxiom pointed that out.