Sage 100

I am getting the database connection error Database Vendor Code 9 error when running the AP aged invoice report. I imported the AP_OpenInvoice into Crystal, but do not see any issues with the dates. Does anyone have the settings in Crystal that will help identify the bad date record? Thanks,

In the past I've gotten Crystal to show the bad date records by unchecking convert database fields null values to default in report options. I do remember it was a pain to try and find the bad records

Do an Excel query of the table pulling the dates out. If it is date related, the query will blow up also. Alnoor (and others) have posted that you can change the ""NULL"" field in the ODBC set up

There is another file involved - I thought it would be the AP_TransactionPaymentHistory file (which I still recommend checking) but the Sage Knowledge Base calls out the other file as the AP_InvoiceHistoryHeader file. Check the InvoiceHistoryHeader file first for the bad date but if none found there I would run the query on the TransactionPaymentHistory file as well.

I was not able to find anything in the open invoice file so I will look at the ones @JoanSerr suggest. Thanks!

What version are we working in? For 440 and above the tabs in customer maintenance should provide you what you seek. It may not be a null value but a bad date value. In customer maintenance - call up the customer and go to the Invoice tab. The invoice date and due date seen in the listing of invoices are coming directly from the OpenInvoice file. Check both fields for corruption, invalid and null dates. A sort by the date columns should move the records in question to the top or bottom of the list (except Feb 29th when not leap year.) Click to the Transaction tab and do the same on the Invoice Transaction dates. The Transaction dates seen on this tab are pulling directly from the records in the TransactionPaymentHistory file. I hope this helps.

Invoices should also reflect a due date, Credit memos do not. Didn't know if you were checking this field value as well. If aging is based upon invoice due date this field value could play a role in the error. Another thing to do is to preview the aging then with the error on screen check the company database to see if the temporary work file generated (AP_AgedInvoiceWrkuuu#############.m4t, wrk followed by user code and 12 numbers). If this file exists, leave the error up and access the temporary work file created through datafile display and maint to see which invoice record was the last to populate. Safe to assume that either this record or the next record in line would be the one creating the error.