Access number format

[jdmartin74]

I have a report displaying numbers for different countries. I can identify each country but wish to display the numbers differently for each. For example, 10,453.56 for a UK number and 10.453,56 for a French/German etc. number.

Access is always using my default locale but I need this to change on a per record basis. Using the field format with something like "##,##0.00" and "##.##0,00" is not handled correctly; it basically reads my locale.

Does anyone know of a solution to this short of simply mashing the strings and doing a search and replace?

 

[SeeThru]

You could wite a peice of code that resets the number format of the report - add it to the on-format ie

select case [Country]
case "UK"
field.format="#,##0.00"
case "GER"
field.format="#.##0,00"
end select

Never tried this, but ought to work.....

 

[jdmartin74]

I have tried this. Access seems to overide based on regional settings still. The format applies but the comma is treated as a thousand seperator and the period as a decimal. Access is obviously reading the local regional settings to get this information.

 

[Remou]

Hi
Here is a small function that might give you some ideas:

Function ForCur(Cur)
Dim IntPart, DecPart
Cur = (Format(Cur, "#,###.00"))
If InStr(1, Cur, ".") > 0 Then
    DecPart = Mid(Cur, InStr(1, Cur, ".") + 1)
Else
    'some problem here
End If

IntPart = Mid(Cur, 1, InStr(1, Cur, ".") - 1)
Do While InStr(1, IntPart, ",") > 0
    Mid(IntPart, InStr(1, IntPart, ","), 1) = "."
Loop
ForCur = IntPart & "," & DecPart
End Function

If you put it in a module and put =ForCur([CurrFld]) in your report.