FormatNumber Function 1

[Mantle51]

Hello,
I've nested the FormatNumber function within my own creation which will be called in the field of a query, however the output never populates trailing zeros.

Function RemoveDecimal(ByVal Price As Long, ByVal NewPrice As Double) As Long
'''I use Price field twice as arguments when calling from query

Price = Price
NewPrice = FormatNumber(NewPrice, 6)

RemoveDecimal = Price & NewPrice

End Function

 

[HandsOnAccess]

There are easier ways to format zeros and decimals
Give numerical examples of initial price format
along with the final format that you wish to have
Give example on How you want the below like to look in terms of number
RemoveDecimal = Price & NewPrice

 

[Mantle51]

OK,
Initial Formatting is for eg. 123.56 - coming from Access table formatted as Number with Auto selected as decimal places. So, in some cases it may read 123.568 etc...

I'd like 123.56 to look like:
123560000 - Remove decimal and carry 6 places after the integer, no matter what the case.

Thanks

 

[HandsOnAccess]

Public Function RemoveDecimal(ByVal Price As Double) As Long
    
    intPart = Mid(Price, 1, InStr(1, Str(Price), ".") - 2)
    decPart = Mid(Price, InStr(1, Str(Price), "."), 6)
    
    If InStr(Str(Price), ".") > 0 Then
        RemoveDecimal = intPart & (decPart * (10 ^ (6 - Len(decPart))))
    Else
        RemoveDecimal = Price & "000000"
    End If
    
End Function

Please Note ByVal Price As Double

 

[PHV]

Why not simply this ?
RemoveDecimal = Int(Price * 10^6)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mantle51]

Well,
I came up with this...which works also

Function RemoveDecimal(ByVal Price As String) As Long
Dim PosDec As Integer, RtTmp As String

If InStr(Price, Chr(46)) = 0 Then
Price = Price & "000000"
RemoveDecimal = Price
Else
PosDec = InStr(Price, Chr(46))
Do Until Len(Price) - PosDec = 6
If Len(Price) - PosDec < 6 Then
Price = Price & "0"
End If
Loop
RtTmp = Right(Price, 6)
RemoveDecimal = Left(Price, PosDec - 1) & RtTmp
End If

End Function