Solved

# GetFormat module

Posted on 2013-06-11
253 Views
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
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

Author Comment

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

LVL 92

Expert Comment

ID: 39239251
Please explain what "rounded properly" would be :)
0

LVL 120

Expert Comment

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

Author Comment

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

Rey Obrero (Capricorn1) earned 400 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 50

Expert Comment

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
``````
/gustav
0

Author Comment

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

Question has a verified solution.

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

### Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
###### Suggested Courses
Course of the Month7 days, 23 hours left to enroll