Sage 100

Bad Date fields in AR_OpenInvoice file. We have a client that has been live for about a month and a half. Version 2013 SP3. For the most part, everything seems to be operating normally. Last week they ran the aging report for the first time and up came the crystal error with ""vendor code 9"". I knew that indicated a bad date field so I had one of my other consultants look in AR_OpenInvoice file, AR_TransactionPaymentHistory and AR_invoiceHistoryHeader he found dozens of bad dates in each of these files and cleaned them up. I thought at the time that it might have been import-related since we imported of open invoices at go live. Yesterday they tried to run the aging again and got the error again. I looked and found an invoice that was entered last week with another bad date (Date was ""2131011"" missing a zero in the year) I looked at the backup we made before cleaning this up last time and discovered that the invoices we fixed were not from our import but ones that were manually being entered, so this is apparently happening quite a often. Has anyone ran into this? I can't keep using DFDM to fix these forever. The Installation is pretty vanilla! it is Sag100 Advanced - no 3rd party modifications or custom programming. There are a lot of custom office changes but the vast majority of those are to the purchase order module. The only other unusual thing I can think of is that they do a lot of drop ship in sales order but that doesn't seem very relevant.

I run into it sporadically, but never on the scale you are describing. I managed to create it myself once, but don't know what I did (typical user, hm).

I would either modify the crystal forms to highlight/bold/red the date field on the printout so that it may stick out, or ideally, use a user defined script to check the date before the order is saved and prevent it from being saved if the month day or year is way off.

Gee, @SteveIwanowski - too bad Sage doesn't do that checking business already, hm? Those date errors irritate the doggy doo right out of me. Happens in pretty much all modules - or at least all 4.x modules. I don't think I've seen it in the legacy programs - so apparently this is an enhancement.

Just helped a reseller yesterday fix dates in AR_InvoiceHistoryDetail after receiving a call about PTQ tables not working as expected. Luckily you had an easy time finding the bad dates. Very difficult for me. I have resorted to dumping dates into a CSV via VI Export (Yes, Virginia, there really is a use for the Export module), then opening it in Excel, then filtering the dates (there were 250K records yesterday. I've had the issue periodically as well.

Jeff, I found them by changing a setting in the ODBC configuration for the SOTAMAS90 driver and created a quick crystal to show me all the date fields in the table then I sort by date and the bad ones rise to the top. You have to change a setting in ODBC configuration to keep from getting the error out but it works great. here is how to change the setting - In windows go into ODBC Admin from administrative tools and on the OPTIONS tab, check the box for ""NULL DATE"" it will allow you to show the all dates even ones that are bad.

@SteveIwanowski are you saying the user might be typing these in? I guess I thought that Sage100 would not accept a date like that; but I guess the 213 is a technically a valid year! I would expect that to become ""0213"" if I typed it in but I have never tried. I need to check that out. the bad dates seem to be primarily in the due date field. I was going at it from the angle that it was an issue with how the due date was generated by Sage but if the user can type it in that way then your idea is a good solution. I guess I can probably even script it disallow date like that, system OR user generated. Thanks!