Source language: Translate to:

Math error involving .00 and .02

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Math error involving .00 and .02

Postby BRobinsonS » Mon Nov 30, 2015 6:56 pm

The App is an accounting program.
This section sums Debits (SumIn) and Credits(SumOut) and calculate the difference (DiffSum)

This Subroutine gives DiffSum error:
dbpSum of SumIn is correct i.e. .00
dbpSum of SumOut is correcot i.e. .02
But DiffSum shows value of error but should be .02 as result
Not sure why.
Works on all other entries.
Also checks to see if the calculation of Journal entries agrees with Account Balance.
Again, this works correctly except of these values i.e. error not equal to .02 (Account Balance).
Code: Select all
:SumInOut

 SetVar "[SumIn]" "0"
 SetVar "[SumOut]" "0"
 SetVar "[DiffSum]" "0"
 If "[NBAccounting.JrDetails.$RecCount]" ">" "0"

  dbpSum "NBAccounting" "JrDetails" "InAmt" "[SumIn]"
  AlertBox "SumIn" "SumIn = [SumIn]"
  dbpSum "NBAccounting" "JrDetails" "OutAmt" "[SumOut]"
  AlertBox "SumOut" "SumOut = [SumOut]"
  If "[SumIn]" ">=" "[SumOut]"
   Math "[SumIn]-[SumOut]" "2" "[DiffSum]"
  EndIf
.  Else
  If "[SumIn]" "<" "[SumOut]"
   Math "[SumOut]-[SumIn]" "2" "[DiffSum]"
  EndIf

 If "[BalanceFld]" "<>" "[DiffSum]"
   AlertBox "Out of Balance" "Account [NBAccounting.Gl.AcctDescription] is out of balance."
 EndIf

Return


Suggestions? Thoughts?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby Neosoft Support » Mon Nov 30, 2015 7:23 pm

If I remove all the database stuff and simplify the code like this it appears to work correctly:

SetVar "[SumIn]" "0"
SetVar "[SumOut]" "0.02"
SetVar "[DiffSum]" "0"
If "[SumIn]" ">=" "[SumOut]"
Math "[SumIn]-[SumOut]" "2" "[DiffSum]"
EndIf
If "[SumIn]" "<" "[SumOut]"
Math "[SumOut]-[SumIn]" "2" "[DiffSum]"
EndIf

Maybe the database contents are different than you expect?
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Math error involving .00 and .02

Postby BRobinsonS » Mon Nov 30, 2015 7:53 pm

The database sums give the correct answers.
Just the calculate of DiffSum for these particular values gives the error.

The fields are all currency and default value = 0 i.e. JrDetails InAmt, OutAmt as BalanceFld in GL.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby BRobinsonS » Mon Nov 30, 2015 8:10 pm

Tried increasing the entries values.
Seems like it works if the sum amount is greater than 1 dollar. ????

This started because I was testing my accounting program and one of my bank accounts had earned 1 penny i.e. .01 interest.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby Gaev » Tue Dec 01, 2015 6:39 am

Brian:

It could be that the Currency field type is messing things up.

Also, I am assuming that you did not use DefineVar for [SumIn] or [SumOut] or [DiffSum].

Tried increasing the entries values.
Seems like it works if the sum amount is greater than 1 dollar. ????

Try something like this as a work around ...
Code: Select all
dbpSum "NBAccounting" "JrDetails" "InAmt" "[SumIn]"
Math "[SumIn]*100" "0" "[SumInCents]"

... and similarly for [SumOut] ... then, when calculating the difference ...
Code: Select all
Math "([SumInCents]-[SumOutCents])/100" "2" "[DiffSum]"


This started because I was testing my accounting program and one of my bank accounts had earned 1 penny i.e. .01 interest.

Your bank pays interest ? :shock:
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Math error involving .00 and .02

Postby BRobinsonS » Tue Dec 01, 2015 9:28 am

Tried using DefineVar for DiffSum.
Didn't help.

Tried workaround without success.
Seem like other accounts had an error then.
May have to live with the error until the 'Interest Income' is more than a dollar.
Which won't happen any time soon.
Just using this to compare the difference in Deposits and Withdrawals Transactions and the balance in the particular account.

My bank is a credit union (personal account) :?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby Gaev » Tue Dec 01, 2015 9:47 am

Brian:

Tried using DefineVar for DiffSum.
Didn't help.

Wasn't suggesting you use it ... but while you are at it, try defining all 3 variables as currency ... and like in Dave's example code, give them a dummy value using SetVar before replacing the contents from the database fields.

Tried workaround without success
Seem like other accounts had an error then..

Looks like Currency field in the database might be the root cause ... can you add numeric fields to the Table ? ... then copy contents from currency fields and do your Math on those values ?

May have to live with the error until the 'Interest Income' is more than a dollar.
Which won't happen any time soon.

When is the next Lotto 6/49 draw ? :)

If you can send a test database that produces these erroneous results to me, I will try and create a simple pub that does just what you posted.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Math error involving .00 and .02

Postby BRobinsonS » Thu Dec 03, 2015 8:21 am

Wow. Used DefineVar for SumIn, SumOut, and DiffSum and the 'error' is gone.

Thanks for your help and insight.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby BRobinsonS » Tue Jan 05, 2016 1:57 pm

Revisiting This topic. Not sure there is an answer.
Though I thought I had it solved earlier.

After putting in a year's worth of transactions, I still have a small puzzle.
I have an account keeping track of small amounts of interest. Started with .01 when problem first showed.
Over the course of entering the transactions the interest amount has gone from .01 to .02 to .03 now at .11
The total of the account increased as well. But using the Math function below if the total was less than 1.00 the value is not calculated.
The total is now 1.01 and using the transaction to add .11 it calculates right.

Code: Select all
** If OutAmt (defined as Currency) equals a decimal value i.e. .09 the resulting CRBalance doesn't calculate **
** CRBalance also defined as currency
** Example: CRBalance = .36 and OutAmt = .09
IfEx "[NBAccounting.Gl.NormalBalance]= CR AND [NBAccounting.JrDetails.OutAmt] > 0"
  Math "[NBAccounting.Gl.CRBalance]+[NBAccounting.JrDetails.OutAmt]" "2" "[NBAccounting.Gl.CRBalance]"
EndIf

dbpSaveEdits "NBAccounting" "gl"

** Read in Help file for Math function you can use -1 for decimal place to let function determine best number of decimal places.
Don't know if this will help.

Just updating. Will see if future interest entries will calculate right.
Doesn't make sense (cents) to me. :lol: :?:
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby Gaev » Tue Jan 05, 2016 3:24 pm

Brian:

Do your investigating in steps ...

a) use this code ...
Code: Select all
//include any DefineVar commands here
SetVar "[NormalBalance]" "???"
SetVar "[OutAmt]" "???"
SetVar "[CRBalance]" "???"

SetVar "[Trace]" "1"
IfEx "[NormalBalance] = CR AND [OutAmt] > 0"
  SetVar "[Trace]" "2"
  Math "[CRBalance]+[OutAmt]" "2" "[CRBalance]"
EndIf
AlertBox "Result" "[Trace] ... [CRBalance]


Try various values in the SetVar commands ... and post results (expected and not expected) here.

b) If all works as expected, use Database Fields in the SetVar commands in place of hard coded values ... so you can zoom in on the root causes.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Math error involving .00 and .02

Postby Neosoft Support » Tue Jan 05, 2016 7:29 pm

What type of field is [NBAccounting.Gl.NormalBalance] and what is "CR" supposed to be?
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Math error involving .00 and .02

Postby BRobinsonS » Wed Jan 06, 2016 1:50 pm

[NBAccounting.Gl.NormalBalance] is a text field either DB for Debit or CR for Credit Normal balance type of account.
It is String(2) in size.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby Neosoft Support » Wed Jan 06, 2016 8:30 pm

If it's a string field, then your IfEx action probably should look like this:

IfEx "[NBAccounting.Gl.NormalBalance]= [#34]CR[#34] AND [NBAccounting.JrDetails.OutAmt] > 0"

I think otherwise the condition would never equal true.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Math error involving .00 and .02

Postby BRobinsonS » Thu Jan 07, 2016 9:55 am

IfEx "[NBAccounting.Gl.NormalBalance]= CR AND [NBAccounting.JrDetails.OutAmt] > 0"
Math "[NBAccounting.Gl.CRBalance]+[NBAccounting.JrDetails.OutAmt]" "2" "[NBAccounting.Gl.CRBalance]"
EndIf

The statement does execute. I tried Gaev suggestion of putting Trace = 1 before the If statement and Trace = 2 inside the if statement.

I the entire routine with comments below
Code: Select all
:UpdateAccountBalance
. update account balance when adding Journal Entry record
. DB are left side accounts and CR are right side accounts and result should be DBs = CRs
. ie double entry accounting
. InAmt increase DBBalance but decreases CRBalance
. OutAmt decreases CRBalance but increases CRBalance
. all JrDetails entries are posted correctly before updating the Balances
. all numbers are Currency i.e. DBBalance,CRBalance,InAmt,OutAmt
. **Problem: if balances are less than 1.00 when adding pennies i.e. .03 etc the new balance is not calculated.
. in fact it seems to be blank.
. All statement execute correctly except when pennies are involved and balance is less than $1.00

dbpFind "NBAccounting" "Gl" "AccountNo" "[NBAccounting.JrDetails.AccountNo]" "ExactMatch=Yes;CaseSensitive=No"

. ONE Normal Balance is DB and InAmt > 0 INCREASES DBBalance
IfEx "[NBAccounting.Gl.NormalBalance]= DB AND [NBAccounting.JrDetails.InAmt] > 0"
   Math "[NBAccounting.Gl.DBBalance]+[NBAccounting.JrDetails.InAmt]" "2" "[NBAccounting.Gl.DBBalance]"
EndIf

.TWO Normal Balance is DB and OutAmt > 0 DECREASES DBBalance
IfEx "[NBAccounting.Gl.NormalBalance]= DB AND [NBAccounting.JrDetails.OutAmt] > 0"
  Math "[NBAccounting.Gl.DBBalance]-[NBAccounting.JrDetails.OutAmt]" "2" "[NBAccounting.Gl.DBBalance]"
EndIf

.THREE Normal Balance is CR and InAmt > 0 DECREASES CRBalance
IfEx "[NBAccounting.Gl.NormalBalance]= CR AND [NBAccounting.JrDetails.InAmt] > 0"
  Math "[NBAccounting.Gl.CRBalance]-[NBAccounting.JrDetails.InAmt]" "2" "[NBAccounting.Gl.CRBalance]"
EndIf

.FOUR Normal Balance is CR and OutAmt > 0 DECREASES CRBalance
IfEx "[NBAccounting.Gl.NormalBalance]= CR AND [NBAccounting.JrDetails.OutAmt] > 0"
  Math "[NBAccounting.Gl.CRBalance]+[NBAccounting.JrDetails.OutAmt]" "2" "[NBAccounting.Gl.CRBalance]"
EndIf

dbpSaveEdits "NBAccounting" "gl"

Return


Not sure how to post image of screen that uses the Subroutine.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Math error involving .00 and .02

Postby Gaev » Thu Jan 07, 2016 1:43 pm

Brian:
The statement does execute. I tried Gaev suggestion of putting Trace = 1 before the If statement and Trace = 2 inside the if statement.
So, we have established that the issue originates in (is confined to) the servicing of the Math commands.

Try these work arounds ...
Code: Select all
SetVar "[DBBalance]" "[NBAccounting.Gl.DBBalance]"
Setvar "[InAmt]" "[NBAccounting.JrDetails.InAmt]"
Math "[DBBalance]+[InAmt]" "2" "[DBBalance]"
AlertBox "DBBalance" "[DBBalance] ... [InAmt]"
If that works as expected, try ...
Code: Select all
SetVar "[DBBalance]" "[NBAccounting.Gl.DBBalance]"
Setvar "[InAmt]" "[NBAccounting.JrDetails.InAmt]"
Math "[DBBalance]+[InAmt]" "2" "[NBAccounting.Gl.DBBalance]"
AlertBox "DBBalance" "[DBBalance] ... [InAmt] ... [NBAccounting.Gl.DBBalance]"

Tell us of your experience.

All statement execute correctly except when pennies are involved and balance is less than $1.00
As a work around, you might consider storing the balances as cents i.e. integer values only ... just have to multiply/divide by hundred when going between DB values and those shown on the screen.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 0 guests