Restricting data displayed in a sub-form

[Courtman]

I am trying to display a logbook in a subform. The logbook will have several entries, most of which are stored in the table as 0.0. When displaying the logbook I only want it to display the fields that have a number greater than 0, all other fields I would like to just show a "-". I cannot make Validation Rules work for this. Can anyone help me please???

 

[cogent1]

You can do this by having unbound calculated fields in place of the actual fields in the subform.

A typical calculation would be:

=IIf(MyField>0,MyField, "-")

 

[Courtman]

Tried this, it just comes up with #Name? The form is linked to the table, would I have to unlink it to make it work, which would mean that the other log data would not display either ....

 

[rich.barry]

It helps to be an Excel User as well to be up with the play on number formats - the help is good in Excel.

Custom format is:
Format for positive;Format for negative;Format for zeros;Format for text

Thus setting the format in your table to:
Data Type : Number
Format #;-#;-

will do what you want and still have everything bound correctly

Happy flying

 

[cogent1]

My solution will work if all the rules are followed, (preceding equals sign, valid field names, square brackets round field names if they contain spaces, etc). Calculated fields on the form are not bound(ie they are not stored in the underlying table).


However, this is all academic if the data lends itself to a custom format as suggested. I'd advise you to apply the formatting at form level rather than table level though.