The Linux Page

QuickBooks and the Invalid Date error!

In my work with MS-Access and QuickBooks, I'm slowly progressing toward incredebilitiousness (some Franglais as we say in French).

I was working on adding Items to the ItemInventory table. All you have to do is an INSERT or an UPDATE.

After I added all three accounts, the INSERT finally worked properly. There are the Income, Cost and Asset accounts attached to each item. These are used to know where you're at with your stock. Pretty cool except that my customer has 6 different status for each item and that determines the true Income, Cost and Asset of the item. Thus, this is close to uselessness (without having to create 6 different items to support of the different statuses.)

Okay. Now the INSERT works, lets move on to the UDPATE. That fails. I am updating the same items... What's wrong? Well!!! There is the QuickBooks errors (at least QODBC says it is from QuickBooks):

There was an error when modifying a ItemInventory list, element "800000DA-1237615127".
QuickBooks error message: Invalid date specified.

The element identifier is correct. That's the one I'm trying to update.

So. Invalid date, hey? Hmmmm... I look at the table again, there are three dates: the usual creation & modification date and the InventoryDate. All three are marked read-only, except for InventoryDate on INSERT. Okay, but I'm not making use of the inventory data because they will use the Add Stock for that purpose.

Now... just in case, I add the InventoryDate and sure enough it still fails, same error.

I look at the QODBC documentation closer and notice that the Accounts fields are all marked False under the MOD column. This means these fields cannot be modified. (Note that the documentation says that two of them are partially modifiable using the FullName; this is most certainly an error!)

So, I removed those three fields from the UPDATE statement and it worked.

Got it? Invalid date === Cannot write that field. Simple, hey!?

And of course that also means you have to go in QuickBooks to update those fields.