Conditional Number Formatting

[distantger]

Hi all, new to the forum and Access and hoping somebody can help.

We have a report where we would like to format a field based on a condition within each record to either display a number as Currency or as a number with 2 decimal points, with the added format that if the number is zero it displays as "-", a dash.

Inserting #,##0.00;(#,##0.00);- into the Format Property of the field works fine and have tried to use an IIF function based on the condition, but no joy.

Appreciate any suggestions

cheers

 

[James Deckert]

what if statement did you use and where did you put it?

 

[distantger]

Hi

Tried to insert the following in the Txt Box Property Sheet in the format line :

=IIF(Time_type="EXP",#,##0.00;(#,##0.00);-,£#,##0.00;(£#,##0.00);-)

where Time_type is the Table fieldname.

Cheers

 

[James Deckert]

is your report based on a query (or have a query for the Record Source? That would be a good place to replace '0' with '-'