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

Answer 3

You can split your database anytime before distribution to your users but I don't know what you mean by "populate."

See the "Splitting your app into a front end and back end Tips" page for info on splitting.. See the free Auto FE Updater utility to make the distribution of new FEs relatively painless.

Answer 4

I was also wondering; I have a CurrencyCode which defines whatever type of currency the Company is in; now all the calculations in VBA are done already. I wanted to know is there a way to write a SQL statement where for example; if the CurrencyCode=GBP then the resultant field is displayed as £23m instead of 23m etc for all the different currencies that are there in the field.

This again, needs to be hard-coded right now; the only thing is, if in the future if I'm not with the company, someone has to add a company in, which doesn't have the code to do that; then what will be the way to deal with? Will they have to have someone that needs to know about how to use SQL or VBA and be able to change; for this I would prefer doing it in SQL if possible because then I could put this in a manual for the database and SQL is much less daunting to use then VBA for beginners.

The resultant values have to be in the same column, so all the different IF's and Wheres need to be part of the same SQL statement, this is because of the reports that are there which use a single column for the different companies with different currencies.

Thanks!