Number formatting in Access

  • Thread starter Thread starter Shelly
  • Start date Start date
S

Shelly

Hi

I understand that in the Regional Settings of Windows there is an option for
Digit Grouping which shows lakhs formatting i.e. 12,34,456.78. How does one
invoke this number formatting say in an Excel spreadsheet or in an Access
database?

TIA

Shelly
 
Shelly,

Well, I might be wrong, but I couldn't find a way to do it in Access
using the Format property or the Format function. Maybe a user-defined
function? The following is a crude, simplistic, but effective example
of such...

Public Function LakhsFormat(InputNumber As Long) As String
Dim strLakhs As String
strLakhs = Format(InputNumber, "#.00")
If InputNumber >= 1000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 6) & "," &
Right(strLakhs, 6)
End If
If InputNumber >= 100000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 9) & "," &
Right(strLakhs, 9)
End If
If InputNumber >= 10000000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 12) & "," &
Right(strLakhs, 12)
End If
LakhsFormat = strLakhs
End Function

Then, you can call this function in your database, for example in
queries or in calculated controls on forms or reports, like this...
LakhsFormat([YourNumberField])
 
Hi,

I think its down to the individual applicaton as to how it displays
numbers.

In an Access table or form, you might use the 'format' property of
field / textbox.

In Excel you can format a cell or range of cells to display numbers in
various formats (including custom formats - but I've not used custom
formats b4).

*The above can be set using table / form designers or properties and by
using code.

Hope that is of help!

Andy
 
Thanx very much Steve,It works like a charm.
Great help friend :)
Steve Schapel said:
Shelly,

Well, I might be wrong, but I couldn't find a way to do it in Access
using the Format property or the Format function. Maybe a user-defined
function? The following is a crude, simplistic, but effective example
of such...

Public Function LakhsFormat(InputNumber As Long) As String
Dim strLakhs As String
strLakhs = Format(InputNumber, "#.00")
If InputNumber >= 1000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 6) & "," &
Right(strLakhs, 6)
End If
If InputNumber >= 100000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 9) & "," &
Right(strLakhs, 9)
End If
If InputNumber >= 10000000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 12) & "," &
Right(strLakhs, 12)
End If
LakhsFormat = strLakhs
End Function

Then, you can call this function in your database, for example in
queries or in calculated controls on forms or reports, like this...
LakhsFormat([YourNumberField])

--
Steve Schapel, Microsoft Access MVP

Hi

I understand that in the Regional Settings of Windows there is an option for
Digit Grouping which shows lakhs formatting i.e. 12,34,456.78. How does one
invoke this number formatting say in an Excel spreadsheet or in an Access
database?

TIA

Shelly
Click to expand...
Click to expand...
 
I was infact looking for something like this. It works very very well.
Thanks a lot.
Shelly

Steve Schapel said:
Shelly,

Well, I might be wrong, but I couldn't find a way to do it in Access
using the Format property or the Format function. Maybe a user-defined
function? The following is a crude, simplistic, but effective example
of such...

Public Function LakhsFormat(InputNumber As Long) As String
Dim strLakhs As String
strLakhs = Format(InputNumber, "#.00")
If InputNumber >= 1000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 6) & "," &
Right(strLakhs, 6)
End If
If InputNumber >= 100000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 9) & "," &
Right(strLakhs, 9)
End If
If InputNumber >= 10000000 Then
strLakhs = Left(strLakhs, Len(strLakhs) - 12) & "," &
Right(strLakhs, 12)
End If
LakhsFormat = strLakhs
End Function

Then, you can call this function in your database, for example in
queries or in calculated controls on forms or reports, like this...
LakhsFormat([YourNumberField])

--
Steve Schapel, Microsoft Access MVP

Hi

I understand that in the Regional Settings of Windows there is an option for
Digit Grouping which shows lakhs formatting i.e. 12,34,456.78. How does one
invoke this number formatting say in an Excel spreadsheet or in an Access
database?

TIA

Shelly
Click to expand...
Click to expand...
 
Back
Top