Display zero as dash on form while retaining the value of zero?

[pjenks]

First off I am rather new to this. I am designing an accounting database. I have forms that are based on tables and there are calculations involved. I need to display character of the value that equal 0 as - for display only but I need to retain that actual value of 0 to accomplish the calculations. Displaying the - instead of 0 is standard practice in accounting. Excel allows you to do this and still retain functionality when calculating; MS Access apparently does not. Dos anybody have a suggestion?

[jforbes]

You could use something like this for your TextBox's Format Property: $#,##0.00;($#,##0.00);-;?

This reference might help Custom numeric formats for the Format function (Access custom web app).

Basicly, you get to supply four different formats. The First part of the format up to the semicolon(;) is the Positive Number Format, the second bit between the first semicolon and second is the Negative Number Format, the third bit is what to use for a number that is Zero, the fourth bit is what to use if the number is Null. This is just for display purposes, so the underlying value is unchanged.

[pjenks]

jforbes, Thanks so much for your reply. I get the concept of what you are saying. However, how do I actually do this. When you say the "TextBox's Format Property" is that something you select in the property sheet (if so, where?) or do you select Format tab up top? When I look at the Format tab at top, I cant find a way to customize it as you suggest. Sorry, I told you I am pretty new at this. Thanks again.

[pjenks]

jforbes. I think I got it now. I put the following in the OnLoad event of the form and it worked! It displays the dash but calculates a zero. Thanks for your help.

Private Sub Form_Load()
[Forms]![frm_JVCreate]![Adjustment].Format = "$#,##0.00;($#,##0.00);-;?"
End Sub

[jforbes]

Glad you got it working.

Another way to set most Properties of a TextBox is by selecting the TextBox and then finding the property in the Property Sheet; the Format property is usually right around the top of the list. If the Property Sheet isn't displayed, you can either right-click on a Control like the TextBox and select Properties or goto the Design Menu and select Property Sheet on the Ribbon.