Why VBA create additional digits to natural figures

[nector]

I have a four digit natural figures in the MS access database and in the query they are also coming four digits accurately but when I run my in the immediate widow in VBA all my figure are shown with an increase from four digits to ten digits which is causing my Json program to pop up an error.

I do not want VBA to do anything with my figures they are already balanced it should leave them alone, Here I'm avoiding to use these listed functions because they are not accurately:

(1) Int
(2) Round
(3) Bankers

Is there a way I can stop VBA from tempering with my figures?

Regards

 

[theDBguy]

Int() should work. For example:

Int([FieldName]*1000)/1000

PS. If you need ending zeroes, you'll also need to use the Format() function.

 

[The_Doc_Man]

One problem is the defaults for the immediate window. In the absence of an explicit format, the immediate window shows you all the possible digits of the number you are generating. From the description of the unwanted behavior, that number was probably not an integer. In a query or table view you can easily format a field for display purposes, but there are separate properties for that effect. The immediate window, by its nature, is intended to show you exactly what numbers you are generating.

In the immediate window, if it wasn't coming out as 4-digit numbers then that was happening because the method you chose and the data type you chose ALSO weren't limited to a 4-digit number.

You should never be using the immediate window to feed JSON anyway. So offhand, the direct answer to the question that I THINK you asked is, "a Qualified NO", unless you use an explicit formatting function in the immediate window. There is no "blanket" format for immediate-window numbers.

 

[Josef P.]

I do not want VBA to do anything with my figures

VBA does not change your numbers. However, as already mentioned in another thread, you must differentiate between the value and its (visual) representation.
You will never see the value of e.g. a double data type as a readable number, but only its conversion to text. You can leave it to the built-in conversion to decide how the representation should look as a string or, as already mentioned by theDBguy, you can define it yourself, e.g. with Format.

For example, if I write 123 here in the forum, all you see at first is text, but you know that the number 123 is meant.

 

[nectorch]

How do I use the format function in VBA? The problem on the attached Json in excel format is that if the parent header totals does not equate to exactly the summation of line totals the program will give an error, this where I'm completely stack.

See attached excel file , the difference is (1200.0000 - 1199.9999) = 0.0001

 

[gemma-the-husky]

@nector
If you are using a real number (double, single), or possibly using a calculation result directly, access may have a problem with representing your number accurately. For instance, 1.1 cannot be represented perfectly in binary.

So it's not stopping access tampering with your figures that's the issue,, it's you managing the process to achieve the required results. Often the best way to do this is to use the "currency" data type even for ordinary numbers, as currency works to 4dps, and just parenting the result without the currency symbol.

Can you give an example of a calculation not looking correct?

 

[nectorch]

So it's not stopping access tampering with your figures that's the issue,, it's you managing the process to achieve the required results. Often the best way to do this is to use the "currency" data type even for ordinary numbers, as currency works to 4dps, and just parenting the result without the currency symbol.

Can you give an example of a calculation not looking correct?


Thank you so much , but how do I avoid the currency at database level?

 

[nectorch]

So it's not stopping access tampering with your figures that's the issue,, it's you managing the process to achieve the required results. Often the best way to do this is to use the "currency" data type even for ordinary numbers, as currency works to 4dps, and just parenting the result without the currency symbol.

Can you give an example of a calculation not looking correct?


Thank you so much , but how do I avoid the currency at database level?

Many thanks Gemma , this has sorted out the mathematics .

Kindly with regards