Access VBA Formatting

Question

Hey all, I managed to integrate my Database quite well with Excel in the end, but in the end after I showed it to the bosses they asked me to develop the forms and reports in Access again. That did not take too long fortunately, so I ended up doing two front ends for one back end Database. But in the end it'll be a fully Access database with only one bit of Excel integration utilized; basically the transfer spreadsheet method to transfer daily end of trade Share price into Access.

Now I've come to a point where the database is pretty much ready to be split and populated.(What's the best way to tackle this, populate first or split first?)

The question I'm asking is below:

This might seem like a fairly simple question but so far, I haven't been helped with google or maybe I'm not using the right keywords in my search so I thought what better place to ask then on here; Is there a way to format the numbers that are generated through some VBA code and placed in a new table in Access, to make them look like:

So if it's 1,000,000 then it would appear as 1m Or if it's 10,000 then it appears as 10k So basically if it has three 0's then it's a K If it has six 0's then it's an M

I have used some VBA initially to format the numbers in the following way:

ChangeInShare = Format(ChangeInShare, "#,##.00")

But then the request came to shorten some numbers down to make the reports look neater.

The final table, takes the values in a Text format btw.

Thank you

Answer 1

You can use the modulo operator to test if the number is dividable by 1000000 or by 1000 and then replace the last zeros.

Maybe this function points you to the right direction:

Public Function fmt(val As Long) As String
  Dim result As String
  If val Mod 1000000 = 0 Then
    result = (val \ 1000000) & "M"
  ElseIf val Mod 1000 = 0 Then
    result = (val \ 1000) & "K"
  Else
    result = val
  End If
  fmt = result
End Function

Then some test calls:

? fmt(471000)
471K
? fmt(4711)
4711
? fmt(4000000)
4M
? fmt(40000)
40K

Answer 2

Hi Muffi D
additional to vanjes acceptable very good answer there is another idea: what about the scientific notation?

Debug.Print FStr(10)
Debug.Print FStr(2000)
Debug.Print FStr(300000)
Debug.Print FStr(40000000)
Debug.Print FStr(5000000000#)
Debug.Print FStr(12)
Debug.Print FStr(2345)
Debug.Print FStr(345678)
Debug.Print FStr(45678901)
Debug.Print FStr(5678901234#)

Function FStr(ByVal d As Double) As String
   FStr = Format(d, "0.####E+0")
End Function

then you will get:

1,0E+1
2,000E+3
3,E+5
4,E+7
5,E+9
1,2E+1
2,345E+3
3,4568E+5
4,5679E+7
5,6789E+9

if you need for Doubles (or Currency) you can go with vanjes answer but use the ModF-function instead:

Function ModF(ByVal value As Double, _
              ByVal div As Double) As Double
   ModF = value - (Int(value / div) * div)
End Function

Function fmtF(ByVal value As Double) As String
  Dim result As String
  If ModF(value, 1000000000) = 0 Then
    result = (value / 1000000000) & "G"
  ElseIf ModF(value, 1000000) = 0 Then
    result = (value / 1000000) & "M"
  ElseIf ModF(value, 1000) = 0 Then
    result = (value / 1000) & "K"
  Else
    result = value
  End If
  fmtF = result
End Function

regards Oops