Rounding Problem

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!
 
J

Jeff Boyce

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.
 
S

SAC

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...
 
J

John W. Vinson

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!!
 
S

SAC

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...
 
S

SAC

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...
 
S

SAC

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...
 
J

John W. Vinson

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.
 
S

SAC

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...
 
S

SAC

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?
 
J

John W. Vinson

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.
 
R

Risse

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...
 
S

SAC

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rounding Problem 1
Rounding and loss of precision 1
Rounding Problem 3
multi-conditional expense tracking problem 2
Rounding Problem 8
Currency rounding problem 6

Top