Access VBA Formatting -

hey all, managed integrate database quite excel in end, in end after showed bosses asked me develop forms , reports in access again. did not take long fortunately, ended doing 2 front ends 1 end database. in end it'll access database 1 bit of excel integration utilized; transfer spreadsheet method transfer daily end of trade share price access.

now i've come point database pretty ready split , populated.(what's best way tackle this, populate first or split first?)

the question i'm asking below:

this might seem simple question far, haven't been helped google or maybe i'm not using right keywords in search thought better place ask on here; there way format numbers generated through vba code , placed in new table in access, make them like:

so if it's 1,000,000 appear 1m or if it's 10,000 appears 10k if has 3 0's it's k if has 6 0's it's m

i have used vba format numbers in following way:

changeinshare = format(changeinshare, "#,##.00") 

but request came shorten numbers down make reports neater.

the final table, takes values in text format btw.

thank you

you can use modulo operator test if number dividable 1000000 or 1000 , replace last zeros.

maybe function points right direction:

public function fmt(val long) string   dim result string   if val mod 1000000 = 0     result = (val \ 1000000) & "m"   elseif val mod 1000 = 0     result = (val \ 1000) & "k"   else     result = val   end if   fmt = result end function 

then test calls:

? fmt(471000) 471k ? fmt(4711) 4711 ? fmt(4000000) 4m ? fmt(40000) 40k