Fix for 2 Calculated Fields that add up wrong

[Binnit]

I have 2 currency fields on a report, data is input by user (not calculated)

1) = £2,030.22
2) = £366.08

I have 2 calculated fields which show 5% of each value

3) £101.51
4) £18.30

I have 2 calculated fields to add fields 1 & 3 and 2 & 4 together
5) £2,131,73
6) £384.38

In the last calculated field I have added 5 & 6 together
7) £2,516.12

The expected result (£2,131.73 + £384.38)= £2,516.11

This is brought about by a rounding of fields 3 & 4.

Question: How can I format field 7 to show the correct value?

I have tried various Rounding options shown in Search threads but none appear to work for this.

Any help appreciated.

Thanks





If IT ain’t working Binnit and Reboot

 

[easyit]

Have you tried the function FORMAT?

 

[Binnit]

easyit
Yep....tried various formats, the problem being is the resulting calculated number extends beyond 3 decimal places when fields 3 & 4 are first calculated.

Field 1 becomes £101.511
Field 2 becomes £18.304

Total £119.815 which however way its formatted becomes £119.82

I want to truncate this at £119.81

Any other ideas?

If IT ain’t working Binnit and Reboot

 

[Binnit]

oohps that should read
easyit
Yep....tried various formats, the problem being is the resulting calculated number extends beyond 3 decimal places when fields 3 & 4 are first calculated.

[bold]Field 3[/bold] becomes £101.511
[bold]Field 4[/bold] becomes £18.304

Total £119.815 which however way its formatted becomes £119.82

I want to truncate this at £119.81

Any other ideas?

If IT ain’t working Binnit and Reboot

 

[Binnit]

Ill get it right in a moment!
easyit
Yep....tried various formats, the problem being is the resulting calculated number extends beyond 3 decimal places when fields 3 & 4 are first calculated.

Field 3 becomes £101.511
Field 4 becomes £18.304

Total £119.815 which however way its formatted becomes £119.82

I want to truncate this at £119.81

Any other ideas?

If IT ain’t working Binnit and Reboot

 

[Eupher]

Try converting your values to Single, i.e. in your calculated controls:

=CSng([Field1]*0.05)
=CSng([Field2]*0.05)
=CSng([Field1]+[Field3])
=CSng([Field2]+[Field4])
etc.

HTH,

Ken S.

 

[Eupher]

Oh, and specify 2 decimal places in the controls' property sheets...

Ken S.

 

[Golom]

I don't know if you are doing this in code, SQL or a report but here's a simple test routine that gives the answers that you are looking for.

Private Sub Command34_Click()
    Dim x1 As Currency, x2 As Currency
    Dim y1 As Currency, y2 As Currency
    x1 = 2030.22
    x2 = 366.08
    y1 = CCur(Format(x1 * 0.05, "0.00"))
    y2 = CCur(Format(x2 * 0.05, "0.00"))
    Debug.Print x1, x2, y1, y2
    Debug.Print x1 + y1, x2 + y2, x1 + y1 + x2 + y2
End Sub

and that yields

2030.22       366.08        101.51        18.3 
2131.73       384.38        2516.11

 

[Binnit]

Eupher
Thanks, unfortunately this has now turned it the other way and dropped a penny on another case!

I have the following:-

1) = £2,798.72
2) = £1290.77

3) £139.94
4) £64.54

5) £2,938.66
6) £1,355.31

7) £4,293.96

The expected result should be £4,293.97

Arghhhhh


If IT ain’t working Binnit and Reboot

 

[Binnit]

Golom
I am doing this in a report, I will experiment with your idea and let you know what happens.
Thanks

If IT ain’t working Binnit and Reboot

 

[Golom]

OK

Private Sub Command34_Click()
    Dim x1 As Currency, x2 As Currency
    Dim y1 As Currency, y2 As Currency
    x1 = 2798.72
    x2 = 1290.77
    y1 = CCur(Format(x1 * 0.05, "0.00"))
    y2 = CCur(Format(x2 * 0.05, "0.00"))
    Debug.Print x1, x2, y1, y2
    Debug.Print x1 + y1, x2 + y2, x1 + y1 + x2 + y2
End Sub

results in

2798.72       1290.77       139.94        64.54 
2938.66       1355.31       4293.97

 

[MajP]

=Fix([taxOne]*100)/100 + Fix([taxTwo]*100)/100

 

[Binnit]

Thanks for the replies, I have finally got this sorted now but not how I expected.

Basically I went back to the report recordsource and ensured all the correct calculations & formats were in place rather that doing them in text boxes.

It seems to have worked as both problem calculations are now correct.
Thanks again

If IT ain’t working Binnit and Reboot

 

[MajP]

You may want to read up on floating point arithmetic to understand why this did not work. Converting to a single as Eupher suggests would not work. In floating point arithmetic 1.0 + 1.0 may not necessarily equal 2. What Golom suggests works
y2 = CCur(Format(x2 * 0.05, "0.00"))
because of the format function, but this would not
y2 = CCur(x2 * 0.05)

 

[Eupher]

MajP,

Thanks for the info. Still learning... *sigh*

Ken S.

 

[Binnit]

Majp
I did manage to recreate Golom's method and agree that it worked, however, it did not seem to work properly when used within the Report textbox environment, it did however when formatting within the underlying report recordsource.

This had me going for about 3 days! but I will remember it next time I get a similar issue.

If IT ain’t working Binnit and Reboot