Currency-varying decimal places (but always min 2 decimals) 3

[rose4567]

I need to format a currency field. I'm hoping someone can help me craft a format or forumla to display up to five decimal places but always display a minimum of two decimal places since this is currency.

Database Value Report Display
35 35.00
35.5 35.50
35.032 35.023
35.3694 35.3694
.0045 .0045
35.00000 35.00


I've looked through a bunch of other posts, but all the code samples I located drop trailing zeros and leaves me with 35 instead of 35.00. Standard formatting of the field does not work because the default currency setting will round my numbers down to two decimal places.

If anyone has any ideas I'd be greatful.

Thank you!

 

[xlbo]

Would suggest writing a little function in the code module

Pass through the field value and test the length of the field and find the "."

typed untested:

Function CreateFormat(byVal TestVal)
dim retVal
dim totLen
Dim DecPlace

totLen = len(TestVal)
DecPlace = instr(".",TestVal)

Select case totLen - DecPlace

Case <=5
retval = "#,##0.00"
Case 6
retVal = "#,##0.000"
Case 7
retVal = "#,##0.0000"
Case 8
retval = "#,##0.00000"
Case Else
retval = "#,##0"
End Select

Return retVal
End Function


In your format expression, use

=code.CreateFormat(Fields!Fieldname.value)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DaveInIowa]

If you want your decimal points to line up, I'd format the original value with "0.00", then place a separate text box to the right of it with the expression "=Code.LastThreeDigits(Fields!yourCurrencyField.Value)"

Function LastThreeDigits(ByVal value As Decimal) As String

    Dim ltd As String = Right(value.ToString("0.00000"), 3)

    If (ltd = "000") Then
        Return String.Empty
    ElseIf (ltd Like "#00") Then
        Return Left(ltd, 1)
    ElseIf (ltd Like "##0") Then
        Return Left(ltd, 2)
    Else
        Return ltd
    End If

End Function

Right justify the actual value's text box and set the right padding to zero.
Left justify the code's text box and set the left padding to zero.
You may then want to play around with the formating of the 2nd text box such as making the font size a little smaller, making it a lighter color, etc.

 

[DaveInIowa]

I just realized my suggestion would introduce rounding errors (the last digit in the original value could be rounded up). It also wouldn't be beneficial if you're rendering to Excel. Sorry 'bout that.

 

[checkai]

Couldn't you just set the format value to

#,###.#####

 

[DaveInIowa]

That's a good idea checkai. Only problem is it doesn't meet the minimum 2 decimals requirement. Instead, how about
0.00###
A star for the tip on setting optional placeholders to the right of the decimal point.

 

[rose4567]

Unfortunately, I could use a set format with "#,###.#####" because the the currency value is 36.00000 it displays as 36. That format drops all zeros. However, I can use it in conjunction with an iif statement to say if it found 36.00000 to display 36.00 otherwise, use the format "#,###.#####"

Here is the code for anyone else running into a similar issue.

=iif(Fields!req_item_type.Value <>"I","","$"&iif(Fields!est_unit_cost.Value like "*.00000", FORMAT(Fields!est_unit_cost.Value,"#,##.#####")&".00",FORMAT(Fields!est_unit_cost.Value,"#,##.#####")))

Thanks to everyone who replied!!

 

[checkai]

Dave's follow up should work.
Format =

#,###.00###

 

[rose4567]

That's much simpler. Thank you so much!

 

[xlbo]

Nice

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question