Subject Re: Linked Table and double precision
Author
Hello,

I may be posting completely off topic with your problem, but not knowing exactly the details of the variables that contain your accounting data, nor the format of the fields that store them (how many of your fields tolerate decimals), ...


How do you use Access on the front end: with forms? I've used it 15 years ago. So, it may have changed. But the fact was that when we affected 5.123456 to a double variable in a Forms, itself stored in a double field, the variable was affected with 5.12345600000458, which was strored in the TDoubleField!?? ==> This leads to problems, with a large \uge  number of entries.

(!!!) ==> Our solution was to format the ad hoc numbers according to the compatibility rules, *before* storing them in a database. As a prerequisite, my experience is that if the numbers in the database do not respect the rounding rules of your comptable standards, the database will not be able to do anything for you because, amho, it is too late and too complicated to do something like a "reverse engineering accounting". it will be a nightmare.

Here is an example of processing *before* insertion in the database (the important thing is not the language, but the "how comments"),  which uses the *round* function of any language (here, Pascal)


type
   TenumRoundingState = (rsAlwaysRoundDown, rsAlwaysRoundUp, rsNoRounding, rsStandardRounding);
    
    .../...
    
{┬žExplanations:
Round to @Value and return the result.
Postulate: "When we want to round to N, we keep the N+1 significant digits and we round them."
For 6 significant digits, the FiPrecision must be set = 7, and apply the half-unit rule to the 7th digit.
And we stick to this this lead rule, if we don't want to have to explain later, the why and the the where of a drift in our numbers...
ps: sorry, it's in Hungarian "Frenchized" notation.
 }
    function TAccount.RoundWithPrecision(Value: extended): extended;
    var
        iMantisse: Integer;
        iMantisseRemplacante: Integer;
        dChiffre_NPlus1_Significatif: Extended;
        dChiffre_NPlus1_SignificatifRemplacant: Extended;
    begin
      if (FiPrecisionMantisse > 0) then begin
        { Deletion of all decimals (i.e. replacement by 0) after the FiPrecision nth decimal.
          Example : 0.333333333333... becomes 0.33333330000000... with FiPrecision=7 }
        iMantisse:= Trunc(Frac(Value) * Power(10, FiPrecisionMantisse));
        dChiffre_NPlus1_Significatif:= Frac(iMantisse / 10);
      end;
      { Rounded from the digit N+1 to the digit N. }
      if (FieRoundingState = rsAlwaysRoundDown) then begin
        dChiffre_NPlus1_SignificatifRemplacant:= 0;
      end
      else if (FieRoundingState = rsAlwaysRoundUp) then begin
        if (dChiffre_NPlus1_Significatif > 0) then
          dChiffre_NPlus1_SignificatifRemplacant:= 1;
      end
      { half-unit rule }
      else if (FieRoundingState = rsStandardRounding) then begin
        dChiffre_NPlus1_SignificatifRemplacant:= Round(dChiffre_NPlus1_Significatif);
      end
      else if (FieRoundingState = rsNoRounding) then begin
        dChiffre_NPlus1_SignificatifRemplacant:= dChiffre_NPlus1_Significatif;
      end;
      { Calculation of the result with rounding. }
      iMantisseRemplacante:= iMantisse - Trunc(dChiffre_NPlus1_Significatif * 10) + Trunc(dChiffre_NPlus1_SignificatifRemplacant * 10);
      Result:= Trunc(Value) + (
                              iMantisseRemplacante
                             ) / Power(10, FiPrecisionMantisse);
    end;


Best regards