200
Has anyone run into not being able to "reconcile" your bank statement, and the message it's giving says, "The difference must be zero before you can reconcile this checkbook" . The difference is in fact zero, but it is a negative zero ($0.00) dollar amount. I'm assuming this has something to do with why it's thinking the difference isn't zero, but I'm not sure how to fix it. When I click on the "Adjustment" button, my 2 amounts match.
*This post is locked for comments
Thank you so much for your response, Susan! This makes perfect sense. About a month or so ago, somehow our decimal places changed to (4) places. It was like that for a couple of weeks, so that must be for sure what the problem is. I have forwarded your response to our GP Tech Group so they can go through the "Resolution" steps.
Thanks again!
Yes, and there is a TechKnowledge on that error. Basically at some point you have imported something in that has more decimals than what you are reconciling. Great Plains has the ability to store 5 decimals. When you reconcile you are only seeing 2. A transaction is sitting in the background with .35550 or something like that and you can't reconcile. You need to look up the TK 851301 or enter in "The difference must be zero.
TK 851301 - Last Review: July 29, 2009 - Revision: 2.2
Error message when you reconcile the checkbook in Select Bank Transactions in Microsoft Dynamics GP: "The difference must be zero before you can reconcile this checkbook"
View products that this article applies to.
When you reconcile the checkbook in Select Bank Transactions in Microsoft Dynami...
When you reconcile the checkbook in Select Bank Transactions in Microsoft Dynamics GP, the difference is displayed as zero, but you receive the following error message:
The difference must be zero before you can reconcile this checkbook.
Back to the top
CAUSE
This problem occurs because third-party products are being used that post the cu...
This problem occurs because third-party products are being used that post the currency amounts that have more than two decimal places.
Back to the top
RESOLUTION
Check whether there are any third-party products that integrate with Microsoft D...
Check whether there are any third-party products that integrate with Microsoft Dynamics GP. If there are, and if any of them post currency amounts that have more than two decimal places, use one of the following resolutions.
Back to the top
Resolution 1
A Check or Deposit amount stored in the TRXAMNT column in the CM20200 (CM Transaction) table may have more than two decimal places being stored. To resolve this problem, follow these steps:
1. Make a backup of your company data.
2. Start Microsoft SQL Query Analyzer or Microsoft SQL Server Management Studio. To do this, follow the appropriate steps for the program that you use.
o Microsoft SQL Server Desktop Engine (also known as MSDE 2000)
Start the Support Administrator Console. To do this, click Start, point to All Programs, point to Microsoft Administrator Console, and then click Support Administrator Console.
o Microsoft SQL Server 2000
Start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
o SQL Server 2005
Start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
o SQL Server 2008
Start SQL Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
3. Click New Query to open a new query pane. Then, select the company database.
4. Run the following statements to determine whether there are records that have a TRXAMNT value that has a nonzero third, fourth, or fifth decimal place value.
The following Statements find the error:
Select TRXAMNT,* from CM20200 where substring(str(TRXAMNT,10,5), patindex('%.%',str(TRXAMNT,10,5))+3, 1) > 0
Select TRXAMNT,* from CM20200 where substring(str(TRXAMNT,10,5), patindex('%.%',str(TRXAMNT,10,5))+4, 1) > 0
Select TRXAMNT,* from CM20200 where substring(str(TRXAMNT,10,5), patindex('%.%',str(TRXAMNT,10,5))+5, 1) > 0
5. If the statements in step 4 return a nonzero result, type the following update statements.
The following statements resolve the issue:
UPDATE CM20200 SET TRXAMNT=(FLOOR((TRXAMNT * 100)))*.01 WHERE TRXAMNT <> 0 and TRXAMNT > 0
UPDATE CM20200 SET TRXAMNT=(CEILING((TRXAMNT * 100)))*.01 WHERE TRXAMNT <> 0 and TRXAMNT < 0
Notes
o The first statement updates positive values. The second statement updates negative values.
o These statements basically truncate the value after the second decimal place. For example, $100.12345 is replaced by $100.12.
Execute the query by clicking the green arrow or by pressing F5.
6. Click New Query to open a new query pane. Then, enter the following select statement.
Select DEX_ROW_ID, CURRBLNC, * from CM00100 where CHEKBKID = 'YYYY'
OUR CHECKBKID is “PROVIDER_REIMB”
Note In this statement, YYYY represents the checkbook ID that you are reconciling.
7. In the CURRBLNC column, make sure that the amount that is stored has only two decimal places.
8. If more than two decimal places are being stored, make a backup of your data, and then update the Current Balance by using the correct amount and the correct number of decimal places. To do this, type the following update statement.
9. UPDATE CM00100 SET CURRBLNC = ZZZ.ZZZZZ
10. WHERE DEX_ROW_ID = ##
Note In this statement, ZZZ.ZZZZZ represents the actual balance amount, and ## represents the actual DEX_ROW_ID value.
Back to the top
Resolution 2
If you follow the steps in resolution 1, and if the Select Bank Transactions window displays a difference value that is "negative zero" (-$0.00), follow steps 1 through 6 in resolution 1 on the other amount fields in the CM20200 table.
To do this, replace TRXAMNT in the statement with the other field names that contain currency amounts. The other fields are as follows:
• ORIGAMT
• ClrdAmt
• Checkbook_Amount
Run the following statements to determine whether there are records that have an ORIGAMT value that has a nonzero third, fourth, or fifth decimal place value.
Select ORIGAMT,* from CM20200
where substring(str(ORIGAMT,10,5), patindex('%.%',str(ORIGAMT,10,5))+3, 1) > 0
Select ORIGAMT,* from CM20200
where substring(str(ORIGAMT,10,5), patindex('%.%',str(ORIGAMT,10,5))+4, 1) > 0
Select ORIGAMT,* from CM20200
where substring(str(ORIGAMT,10,5), patindex('%.%',str(ORIGAMT,10,5))+5, 1) > 0
If the previous statements return a nonzero result, type the following update statements.
UPDATE CM20200 SET ORIGAMT=(FLOOR((ORIGAMT * 100)))*.01
WHERE ORIGAMT <> 0 and ORIGAMT > 0
UPDATE CM20200 SET ORIGAMT=(CEILING((ORIGAMT * 100)))*.01
WHERE ORIGAMT <> 0 and ORIGAMT < 0
Note The first statement updates positive values. The second statement updates negative values.
The Select Bank Transactions window should now display a difference amount of "positive zero" ($0.00).
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
