KB 851301 - 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"

1. Start Microsoft SQL Server Management Studio, and click the New Query button to open a query window.
   
2. Execute the following script against the company database to determine whether there are records that have amounts with a non-zero third, fourth, or fifth decimal place value:
   
    Note: If your currency for the checkbook has 2 decimals, the '3' in the script below on each line will check the 3rd position for a fraction. So if your currency has 3 decimal places, adjust the script to '4', etc.....

   select * from CM20200 where right(ClrdAmt,3)<>0
   or right(TRXAMNT,3)<>0
   or right(ORIGAMT,3)<>0
   or right(Checkbook_Amount,3)<>0
   select * from CM20400 where right(DEBITAMT,3)<>0
   or right(CRDTAMNT,3)<>0
   select * from CM20500 where right(StmntBal,3)<>0
   or right(CUTOFFBAL,3)<>0
   or right(ClrePayAmt,3)<>0
   or right(ClrdDepAmt,3)<>0
   or right(ClrdDepAmt,3)<>0
   or right(Cleared_Difference,3)<>0
   or right(OUTPAYTOT,3)<>0
   or right(OUTDEPTOT,3)<>0
   or right(IINADJTOT,3)<>0
   or right(DECADJTOT,3)<>0
   or right(ASOFBAL,3)<>0
   select * from CM20501 where right(TRXAMNT,3)<>0
   select * from CM20201 where right(ORCHKTTL,3)<>0
   or right(Orig_Credit_Card_Total,3)<>0
   or right(Originating_Cash_Total,3)<>0
   or right(Originating_Deposit_Amou,3)<>0
   or right(Originating_Checkbook_Am,3)<>0
   

   
   Note: Be sure to scroll across on any records returned to take note all the 'amount' columns for any records returned.
   
   
3. In addition, execute this script to review the amounts in the Checkbook Master table as well:

   select CURRBLNC,* from CM00100 where CHEKBKID = 'xxx'
   --modify the xxx placeholder with your checkbook ID. 

   
   
4. Make a current restorable backup of the company database before you continue, or do this in a test environment first. (Important!! Do not skip this step.)
   
   
5. Depending on what tables/fields you found the fraction in, read both methods below and use the appropriate method to resolve the issue:
   
   Method 1: Update each field one at a time directly in SQL to remove the fractional decimal:
   You can simply update the amounts found by the scripts above directly in the SQL Table(s) using a script similar to the example below. After updating the amounts to remove the unwanted fraction, then refresh the Select Bank Transactions window to see if the issue is resolved. 
   
   Here is an example script to update one field at a time:
   

   UPDATE CMXXXXX SET YYYYY = '$.$$000' where DEX_ROW_ID = x
   
   
   
   --Before executing this script, update the placedholders as needed: 
    -Modify the CMXXXXX placeholder with the correct table name; 
    -Modify the YYYYY placedholder with the correct field name in that table; 
    -Modify the $.$$000 with the correct amount with 2 decimal places,
    -Modify the x with the specific Dex Row ID for the record that you need to modify. 
   
   Repeat as many times as needed to update each field/table returned by the script in step 2
   and/or step 3 that has more than 2 decimal places.

   
   Note: Be consistent. If you update any amount(s) in a table found by the script in step 2 for a Checkbook_Amt field, then you may need to also update the current checkbook balance value in the CM00100 Checkbook Master table in the same direction. Also, be sure to have all users out of Microsoft Dynamics GP before running an update script against the Checkbook Master table. You don't want anyone to post and change the amount of the current balance for the checkbook at the exact same time that you are doing this maintenance. 
   
   Method 2: Sum field totals to see if any totals have a fractional decimals. 
   Execute the below script to verify the field totals by type. Review to see if any have more decimals than the currency ID you are using. In addition, if the records were imported in or from a 3rd party software, then you may have imported in amounts with more decimals than allowed in GP. In this case, you will need to calculate the 'net difference' and update a single record for this difference so that you balance to $0.00000 exactly. You may need to open a support incident if you need further assistance to do this. If you do this yourself, be sure to do this in a test company first. 
   
   Here is an example script to sum each of the amount fields for the CM20200 table by type:

select Distinct(CMTRXTYPE)as 'Type',SUM(TRXAMNT)as 'TRXAMNT', SUM (ORIGAMT)as 'ORIGAMT', SUM(Checkbook_Amount)as 'Checkbook_Amount' from CM20200 where CHEKBKID='XXX' group by CMTRXTYPE
--update the XXX placeholder with the appropriate checkbook ID. 

Additional:

sp_helpsort