How to set decimal separator in MS Access using VBA?

Question

My software creates PAIN001.XML files directly from an Access financial database. The decimal separator must always be a dot. The numbers are formatted with:

MyText = Format(MyNumber, "#0.00")

However, the format string's dot is automatically replaced by the system decimal separator, which might be "," instead of "." !

In Excel there are easy solutions, for example:

Application.DecimalSeparator = "."
...

However, MS Access doesn't recognize this application property. Is there a simple way to define a decimal separator within Access vba code ?

Of course, one can create a function which scans each MyText number for wrong decimal separators and replaces them with a dot, but this function would have to be called separately for each number, slowing down the code quite a lot…

Gustav · Accepted Answer · 2019-08-22 08:44:25Z

2

The decimal separator must always be a dot.

Then use Str:

MyText = Str(MyNumber)

To convert such a string to a number use Val:

MyNumber = Val(MyText)

edited Aug 22, 2019 at 8:44

answered Aug 21, 2019 at 17:48

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bughater Over a year ago

Thanks, but this doesn't resolve the question because when making text from numbers, Access always uses the default decimal separator. This is also the case when using MyText=Format(123, "#0.00"): the "." is not interpreted as dot, but as decimal separator - that is, MyText is 123,00 when the decimal separator is "," !

Gustav Over a year ago

It certainly does answer your original question. Of course, it doesn't answer your new question. And your assumption, that Access (VBA) always uses the default decimal separator, is not correct. See edited answer, please.

Vityata · Accepted Answer · 2019-08-21 17:16:27Z

1

I guess the problem is not solveable with the decimal separator Application.DecimalSeparator = ".", even if it was supported by the Access library. It is a rather complicated issue, for the non-US users, as we are used to have , as a decimal separator.

In general, VBA considers only . as a decimal separator. Without taking care of the application default separator, the location of the user and their settings. Thus, some interesting cases could happen:

Sub TestMe()
    Dim myText As String
    myText = "123,42"
    Debug.Print Replace(Format(myText, "#0.00"), ",", ".")
End Sub

A possible solution, that I have implemented some time ago was to use Replace() and to replace as in the gif above. It could be a bit slow indeed, but taking into account the usage of VBA and Access, extreme speed is not something the app could achieve anyway.

answered Aug 21, 2019 at 17:16

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Bughater Over a year ago

Thanks, I guessed this is the only way around the problem. To minimize the impact on speed, I implemented it by first retrieving once the system decimal separator DecSep=Right((Format(0, "0."), 1) then calling a formatting function BankSum(Amount,DecSep) each time necessary: If DecSep = "." Then BankSum = Format(Amount, "#0.00") Else BankSum = Replace(Format(Amount, "#0.00"), DecSep, ".") End If As formatting is done in a single step, the effect on speed is negligible. NB: in Switzerland both "." or "," are usual - but for internet banking XML files this must always be "." !

Vityata Over a year ago

@Bughater - well, if it works for you, then its ok. Still, think of some input, using thausands separator like 100.000,32. This may really hit the fan.

Collectives™ on Stack Overflow

How to set decimal separator in MS Access using VBA?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related