Do Not Display a Zero (0) Value

[TammyT]

I am creating a report based on a table that has the following fields:

Item Number (text field as some items include a letter, ie 120a)

CWICARef1 (text field - as some items include a letter, ie 120a)

CWIQARef2 (text field - as some items include a letter, ie 120a)

PMRef1 (Number field - type Integer)

PMRef2 (Number field - type Integer)


The field PMRef2 has a default value of 0, because it's a number field & I can't change that (can I?); however, on the report, I don't want all those zeros - just the "real" values for the few records that have a value in that field.

How do I tell the report to not display the zero values? Or is there a way to set the default to blank, & then nothing would display unless there is an actual value?

I hope this makes sense to someone out there!

+Tammy

 

[RoyVidar]

You can remove the default value. You will probably also have to alter the required property of the field too.

But you can also use the format property of the control in the report, too. For instance, try something like this:

[tt]# ##0,# ##0,""[/tt]

Roy-Vidar

 

[TammyT]

I'm sorry - I'm not following you.

The default value is blank, but it still displays a 0; the required property is already set to No, which is the correct option.

As for the format property - could you explain what you wrote there?

 

[RoyVidar]

Altering default value in a report control, does not alter the value in the field the control is bound to. So if there are zeros in the table, then the control receives those, and you can handle those for instance through altering the format property of the control.

While holding the shift key down, I hit the key marked "3", which on my keyboard produces octothorpe, then a space, then the "3" while holding shift again... and the other characters, effectively producing the pattern above. I typed this while having the cursor within the format property of a text control. Incidently, where I recide, I'll have to use semicolon in stead of comma, due to the decimalseparator used here. The pattern used within the format property of a text control bound to a numeric field, should influence how the values are displayed. The first part for positive values, the next for negative values, the third for zero, and if another comma (semicolon) is added, you can also decide what to show if the field is Null.

Roy-Vidar

 

[TammyT]

When I type those characters in the Format Property of that field on the report & then tab out of the field, Access changes the display to

# 00, #,000,

& then just displays more 0's

Isnt' there some simple code for the On Load event that can say if the value of the field = 0, then make it invisible?

 

[RoyVidar]

Then try with semicolons in stead of comma

Reports doesn't have on load, but on open, but if you whish to do something programatically, then you'll need the on format event of the section in which the controls reside.

Roy-Vidar

 

[TammyT]

That's what I was missing! I was able to insert the code:

If PMRef2.Value = 0 Then
PMRef2.Visible = False
Else: PMRef2.Visible = True
End If

...into the On Format event for the section, & that worked exactly how I needed it to - thanks!!

+Tammy