Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    8

    Unanswered: Formatting Currency

    Hi,

    I want to format a field in VBA, the field displays a message on the screen:

    For example: Jown owes $1000.92 due 01/01/2009

    How Ive got this is txtField = strMessage where

    txtField is a text box on the form and strMessage is a variable.

    Code as follows:

    Private function AmountOwing(byval strPerson as string, byval dtnDate as Date) as String
    dim strMessage as string

    dtnDate = format(dtnDate,("DD/MM/YYYY"))

    AmountOwing = strPerson & " " & " owes " &
    format("100.92","Currency") & " " & dtnDate
    end function

    There is no problem with the code however if the amount owing is $100 then it will display as $100.00, how do I get rid of the .00 using the format command?

    Also. if the amount is $100.70 I dont want the last zero stripped, ie: $100.7.

    So, for example,

    if amount is $100 then display $100 and not $100.00
    If amount is $100.70 then display $100.70 and not $100.7
    If amount is $100.16 then display $100.16

    Can this be achieved by the format command for currency?

    Cheers

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I haven't tested this, but try this:
    Code:
    Private function AmountOwing(byval strPerson as string, byval dtnDate as Date, byval curAmountOwed as currency) as String
    
    AmountOwing = Nz(strPerson,"") & " owes "
    
    if int(curAmountOwed) = curAmountOwed then
        AmountOwing = AmountOwing  & format(curAmountOwed,"$#,##0;($#,##0)")
    else
        AmountOwing = AmountOwing  & format(curAmountOwed,"$#,##0.00;($#,##0.00)")
    end if
    
    AmountOwing = AmountOwing & " due " & format(dtnDate,"DD/MM/YYYY")
    
    end function
    Check out User-Defined Numeric Formats for more information.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the format function in the help system
    but offhand I'd suggest.....

    AmountOwing = strPerson & " " & " owes $" &
    format("100.92","#,##0") & " " & dtnDate

    you may want to consider what rounding rule you want to apply, that may need another function to chop off the trailing decimals... again have a look at the hep system fix or integer may do the job, failing that you may have to write your own function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •