Rounding Problem

  • Thread starter Thread starter SAC
  • Start date Start date
S

SAC

Access 2003

I'm off by a penny. Debit and Credit rows in the table are currency, 2
decimals. Here's the query:

SELECT tblARINH.Custkey,
tblARINH.Date AS TransDate,
tblARINH.Key AS TransKey,
tblARINH.InvNo AS [Number],
tblARINH.ChkNo,
tblARINH.Desc,
tblARINH.Debit,
tblARINH.Credit,

====> Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And [Date]<=#"
& [Transdate] & "# "),2) AS RunDeposit,

====> IIf(IsNull(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# ")),0,
====> Round(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2)) AS RunPayments,

====> [RunDeposit]-[RunPayments] AS Balance


FROM tblARINH
ORDER BY tblARINH.Custkey, tblARINH.Date, tblARINH.Key;

Any idea for a fix for this?

Thanks for your help!
 
If this were mine, I'd probably start out using a pair of queries to do the
calculations.

If you use queries, do you get the same 1 penny difference?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Do you mean like this:

SELECT tblARINH.Custkey,
tblARINH.Debit,
tblARINH.Date AS TransDate,
Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And [Date]<=#" &
[Transdate] & "# "),2) AS RunDeposit
FROM tblARINH


If so, I still get the same number.

Or is there something else I should do?

Thanks.

Jeff Boyce said:
If this were mine, I'd probably start out using a pair of queries to do
the calculations.

If you use queries, do you get the same 1 penny difference?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

SAC said:
Access 2003

I'm off by a penny. Debit and Credit rows in the table are currency, 2
decimals. Here's the query:

SELECT tblARINH.Custkey,
tblARINH.Date AS TransDate,
tblARINH.Key AS TransKey,
tblARINH.InvNo AS [Number],
tblARINH.ChkNo,
tblARINH.Desc,
tblARINH.Debit,
tblARINH.Credit,

====> Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2) AS RunDeposit,

====> IIf(IsNull(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# ")),0,
====> Round(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2)) AS RunPayments,

====> [RunDeposit]-[RunPayments] AS Balance


FROM tblARINH
ORDER BY tblARINH.Custkey, tblARINH.Date, tblARINH.Key;

Any idea for a fix for this?

Thanks for your help!
Click to expand...
Click to expand...
 
Access 2003

I'm off by a penny. Debit and Credit rows in the table are currency, 2
decimals. Here's the query:

SELECT tblARINH.Custkey,
tblARINH.Date AS TransDate,
tblARINH.Key AS TransKey,
tblARINH.InvNo AS [Number],
tblARINH.ChkNo,
tblARINH.Desc,
tblARINH.Debit,
tblARINH.Credit,

====> Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And [Date]<=#"
& [Transdate] & "# "),2) AS RunDeposit,

====> IIf(IsNull(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# ")),0,
====> Round(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2)) AS RunPayments,

====> [RunDeposit]-[RunPayments] AS Balance


FROM tblARINH
ORDER BY tblARINH.Custkey, tblARINH.Date, tblARINH.Key;

Any idea for a fix for this?

Thanks for your help!
Click to expand...

A Currency datatype field is stored with four decimal places, even if they're
not all shown. It's possible that you have some calculation that goes into
either Debit or Credit that's leaving some fractional cents - e.g. you see
$21.85 but what's actually stored in the table is $21.8525. These fractional
cents will add up and throw your totals off.

The solution is to always use the Round() function in your calculations to
store the value rounded to two decimals. If you already have data in the table
with the problem, you can run an Update query updating Debit to Round([Debit],
2)... back up your database first of course!!
 
Excellent!!

Thanks!

John W. Vinson said:
Access 2003

I'm off by a penny. Debit and Credit rows in the table are currency, 2
decimals. Here's the query:

SELECT tblARINH.Custkey,
tblARINH.Date AS TransDate,
tblARINH.Key AS TransKey,
tblARINH.InvNo AS [Number],
tblARINH.ChkNo,
tblARINH.Desc,
tblARINH.Debit,
tblARINH.Credit,

====> Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And
[Date]<=#"
& [Transdate] & "# "),2) AS RunDeposit,

====> IIf(IsNull(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# ")),0,
====> Round(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2)) AS RunPayments,

====> [RunDeposit]-[RunPayments] AS Balance


FROM tblARINH
ORDER BY tblARINH.Custkey, tblARINH.Date, tblARINH.Key;

Any idea for a fix for this?

Thanks for your help!
Click to expand...

A Currency datatype field is stored with four decimal places, even if
they're
not all shown. It's possible that you have some calculation that goes into
either Debit or Credit that's leaving some fractional cents - e.g. you see
$21.85 but what's actually stored in the table is $21.8525. These
fractional
cents will add up and throw your totals off.

The solution is to always use the Round() function in your calculations to
store the value rounded to two decimals. If you already have data in the
table
with the problem, you can run an Update query updating Debit to
Round([Debit],
2)... back up your database first of course!!
Click to expand...
 
Thanks, Jeff!!

Jeff Boyce said:
If this were mine, I'd probably start out using a pair of queries to do
the calculations.

If you use queries, do you get the same 1 penny difference?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

SAC said:
Access 2003

I'm off by a penny. Debit and Credit rows in the table are currency, 2
decimals. Here's the query:

SELECT tblARINH.Custkey,
tblARINH.Date AS TransDate,
tblARINH.Key AS TransKey,
tblARINH.InvNo AS [Number],
tblARINH.ChkNo,
tblARINH.Desc,
tblARINH.Debit,
tblARINH.Credit,

====> Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2) AS RunDeposit,

====> IIf(IsNull(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# ")),0,
====> Round(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2)) AS RunPayments,

====> [RunDeposit]-[RunPayments] AS Balance


FROM tblARINH
ORDER BY tblARINH.Custkey, tblARINH.Date, tblARINH.Key;

Any idea for a fix for this?

Thanks for your help!
Click to expand...
Click to expand...
 
John,

If I set the field in the table for Currency, 2 Decimals...then will it only
store 2 decimals or do I always need to make an allowance for the storing
and subsequent rounding of 4 decimals?

Thanks very much for your help.


John W. Vinson said:
Access 2003

I'm off by a penny. Debit and Credit rows in the table are currency, 2
decimals. Here's the query:

SELECT tblARINH.Custkey,
tblARINH.Date AS TransDate,
tblARINH.Key AS TransKey,
tblARINH.InvNo AS [Number],
tblARINH.ChkNo,
tblARINH.Desc,
tblARINH.Debit,
tblARINH.Credit,

====> Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And
[Date]<=#"
& [Transdate] & "# "),2) AS RunDeposit,

====> IIf(IsNull(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# ")),0,
====> Round(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2)) AS RunPayments,

====> [RunDeposit]-[RunPayments] AS Balance


FROM tblARINH
ORDER BY tblARINH.Custkey, tblARINH.Date, tblARINH.Key;

Any idea for a fix for this?

Thanks for your help!
Click to expand...

A Currency datatype field is stored with four decimal places, even if
they're
not all shown. It's possible that you have some calculation that goes into
either Debit or Credit that's leaving some fractional cents - e.g. you see
$21.85 but what's actually stored in the table is $21.8525. These
fractional
cents will add up and throw your totals off.

The solution is to always use the Round() function in your calculations to
store the value rounded to two decimals. If you already have data in the
table
with the problem, you can run an Update query updating Debit to
Round([Debit],
2)... back up your database first of course!!
Click to expand...
 
John,

If I set the field in the table for Currency, 2 Decimals...then will it only
store 2 decimals or do I always need to make an allowance for the storing
and subsequent rounding of 4 decimals?
Click to expand...

A Currency datatype will always store four decimals. Setting the Decimals
property to 2 will cause it to only display two of those four, but they'll all
four always be there. You must round your calculations to prevent hidden
digits from adding up.

You can instead use a Number... Decimal datatype and set the Scale to 2.
 
Thanks, John!

John W. Vinson said:
A Currency datatype will always store four decimals. Setting the Decimals
property to 2 will cause it to only display two of those four, but they'll
all
four always be there. You must round your calculations to prevent hidden
digits from adding up.

You can instead use a Number... Decimal datatype and set the Scale to 2.
Click to expand...
 
So if I want to avoid having to round...

Data Type = Number
Field Size = Decimal
Format = Currency
Precision = ???? Maybe 20 or something?
Scale = ???? Maybe 20 or something?
Decimal Places = 2

?? Would this work?
 
So if I want to avoid having to round...

Data Type = Number
Field Size = Decimal
Format = Currency
Precision = ???? Maybe 20 or something?
Scale = ???? Maybe 20 or something?
Decimal Places = 2
Click to expand...

The precision is the number of digits you want included: e.g. if you want
values up to 999,999,999.99 you would use 11. The scale is the number of
digits kept after the decimal point, so 2. There are limits to the precision,
see the Help.
 
SAC said:
John,

If I set the field in the table for Currency, 2 Decimals...then will it
only store 2 decimals or do I always need to make an allowance for the
storing and subsequent rounding of 4 decimals?

Thanks very much for your help.


John W. Vinson said:
Access 2003

I'm off by a penny. Debit and Credit rows in the table are currency, 2
decimals. Here's the query:

SELECT tblARINH.Custkey,
tblARINH.Date AS TransDate,
tblARINH.Key AS TransKey,
tblARINH.InvNo AS [Number],
tblARINH.ChkNo,
tblARINH.Desc,
tblARINH.Debit,
tblARINH.Credit,

====> Round(DSum("Debit","tblARINH","CustKey=" & [CustKey] & "And
[Date]<=#"
& [Transdate] & "# "),2) AS RunDeposit,

====> IIf(IsNull(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# ")),0,
====> Round(DSum("Credit","tblArinh","CustKey=" & [CustKey] & "And
[Date]<=#" & [Transdate] & "# "),2)) AS RunPayments,

====> [RunDeposit]-[RunPayments] AS Balance


FROM tblARINH
ORDER BY tblARINH.Custkey, tblARINH.Date, tblARINH.Key;

Any idea for a fix for this?

Thanks for your help!
Click to expand...

A Currency datatype field is stored with four decimal places, even if
they're
not all shown. It's possible that you have some calculation that goes
into
either Debit or Credit that's leaving some fractional cents - e.g. you
see
$21.85 but what's actually stored in the table is $21.8525. These
fractional
cents will add up and throw your totals off.

The solution is to always use the Round() function in your calculations
to
store the value rounded to two decimals. If you already have data in the
table
with the problem, you can run an Update query updating Debit to
Round([Debit],
2)... back up your database first of course!!
Click to expand...
Click to expand...
 
Excellent! Thanks.


John W. Vinson said:
The precision is the number of digits you want included: e.g. if you want
values up to 999,999,999.99 you would use 11. The scale is the number of
digits kept after the decimal point, so 2. There are limits to the
precision,
see the Help.
Click to expand...
 
Back
Top