special format number

[sal21]

Actually i format this rs1 with:

Format(RS1.Fields(7).Value, "#,##0.00")

if the value of rs1 is 0 i have 0,00

but i need this new format:

000.000,00

how to?

[Rudi]

You could try using an IF statement.

If RS1.Fields(7).Value > 0 then
Format(RS1.Fields(7).Value, "#,##0.00")
ElseIf RS1.Fields(7).Value = 0
Format(RS1.Fields(7).Value, "000,000.00")
End If

[HansV]

Or even

Code: Select all

    If RS1.Fields(7).Value > 0 then 
        MyVar = Format(RS1.Fields(7).Value, "#,##0.00")
    ElseIf RS1.Fields(7).Value = 0
        MyVar = "000,000.00"
    End If

[PJ_in_FL]

What Office application is this in? If Excel, you can skip all the If statements and apply custom formatting directly to the cell.

The Excel custom format is capable of applying formatting based on values <0, >0 and =0.

From Excel's Help:

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

If you're trying to format data in VBA for a cell in Excel, let Excel do the formatting with the custom format of:

Code: Select all

#,##0.00;;"000,000.00"

which will use your format for any positive number and the string "000,000.00" for any cell = 0.