?
Solved

GetFormat module

Posted on 2013-06-11
7
Medium Priority
?
260 Views
Last Modified: 2013-06-12
Experts,

I have this function that rounds large numbers.  It  works fine except the billions are not rounded properly.

ie:  2,699,458,000.00
rounds to 2.699458B

Public Function GetFormat(varField) As String
'USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B
    Select Case Len(varField)
    Case 7 To 9
        GetFormat = FormatNumber(varField / 1000000, 0) & "MM"
    Case 10
        GetFormat = varField / 1000000000 & "B"
End Select
End Function

how can I make
2,699,458,000.00
round to 2.7B?

thank you
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:pdvsa
ID: 39239193
I use the function like this:
AmtOverlay: GetFormat([contractamt])
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39239251
Please explain what "rounded properly" would be :)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39239257
use this function


Public Function GetFormat(varField) As String
'USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B
varField = CDec(Replace(varField, ",", ""))
    Select Case Len(varField)
    Case 7 To 9
        GetFormat = FormatNumber(varField / 1000000, 0) & "MM"
    Case 10 To 12
        GetFormat = FormatNumber(varField / 1000000000, 1) & "B"
End Select
End Function
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:pdvsa
ID: 39239294
Capricorn,

thanks.  do i need to handle nulls?  I get a runtime error 94 "Invalid use of Null".  The debugger highlights the varField line.  

thank you.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 39239732
Public Function GetFormat(varField) As String
'USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B

if varField & ""="" then GetFormat="": Exit Function

varField = CDec(Replace(varField, ",", ""))
    Select Case Len(varField)
    Case 7 To 9
        GetFormat = FormatNumber(varField / 1000000, 0) & "MM"
    Case 10 To 12
        GetFormat = FormatNumber(varField / 1000000000, 1) & "B"
End Select
End Function
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39240501
Just replace FormatNumber with Format and brush up a little:
Public Function GetFormat(ByVal varField As Variant) As String

' USED WITH AN UNBOUND FIELD ON THE RPT AND APPENDS MM OR B

    If IsNumeric(varField) Then
        Select Case Len(varField)
            Case 7 To 9
                GetFormat = Format(varField / 10 ^ 6, 0) & "MM"
            Case 10 To 12
                GetFormat = Format(varField / 10 ^ 9, 0) & "B"
        End Select
    End If

End Function

Open in new window

/gustav
0
 

Author Comment

by:pdvsa
ID: 39242042
Capricorn, very nice.  it rounded 2,699,458,000.00  to 2.7B, which is exactly what I needed.  

Gustav:  that worked too (but it did round the 2.7B to 3B).  

thank you
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question