[QODBC Desktop] Bill DueDate Not Respected When Using TermsRefListID via QODBC/MS SQL

Problem Description:

When inserting a Bill record via QODBC (using OPENQUERY from MS SQL Server or ODBC), the DueDate field is ignored and QuickBooks automatically sets the due date equal to the invoice/TxnDate.

Affected products: QODBC Desktop, QRemote Server, MS SQL Server, QuickBooks Desktop
Expected outcome: The DueDate specified in the INSERT query is correctly applied in QuickBooks.

Symptoms:

• A Bill is created via QODBC OPENQUERY INSERT with an explicit DueDate value.
• The Bill appears in QuickBooks, but the due date equals the TxnDate (invoice date) regardless of the DueDate value sent.

Cause:

1. TermsRefListID set to "Due on receipt." When the TermsRefListID field in the Bill INSERT query references a payment term of "Due on receipt," QuickBooks overrides any manually specified due date and automatically sets it equal to the TxnDate.
2. Conflict between manual date and terms. If you explicitly provide a DueDate, there is no need to include TermsRefListID — QODBC will apply the explicit date. Including both with conflicting values causes QuickBooks to honour the terms ("Due on receipt") over the explicit date.

Environment:

Resolution:

1. Remove TermsRefListID from the INSERT query. If you are explicitly providing a DueDate value, do not include the TermsRefListID field in your Bill INSERT statement. QODBC will use your explicit date instead of trying to apply payment terms.
2. Verify the updated query works. The DueDate should now be correctly applied in QuickBooks.

Example — Without TermsRefListID (fixed):

-- Bill header INSERT without TermsRefListID
INSERT OPENQUERY ([DSN_NAME], 'SELECT "APAccountRefListID", "Memo", "DueDate", "TxnDate", "RefNumber", "VendorRefListID" FROM "Bill" where "TxnID" = ''0'' ')
VALUES ('60001-XXXX', 'Memo text', {d'2025-02-18'}, {d'2025-02-17'}, 'REF001', '80000489-XXXX')

-- Expense line items can still reference the vendor separately
INSERT OPENQUERY ([DSN_NAME], 'SELECT "ExpenseLineMemo", "ExpenseLineAccountRefListID", "ExpenseLineAmount", "FQSaveToCache" FROM "BillExpenseLine" where "VendorRefListID" = ''80000489-XXXX'' ')
VALUES ('Memo', 'AccountID', 150.00, 1)

-- Note: Bill header line no longer includes TermsRefListID or DueDate will be honored from the explicit value above.

Example Scenarios:

Scenario 1 — Vendor with Net-30 terms, but customer wants custom due date:

A company normally pays a vendor on Net-30 terms. However, for a particular batch of bills, they want to set custom due dates. The original query included TermsRefListID (Net-30) plus explicit DueDate. Removing TermsRefListID allows the explicit DueDate to be applied correctly.

Scenario 2 — Vendor with "Due on receipt" terms:

If a vendor's default term in QuickBooks is "Due on receipt," including TermsRefListID will always force the due date to match TxnDate. Removing it lets QODBC use the explicit DueDate value instead.

Additional Notes:

• When to keep TermsRefListID: If you want QuickBooks to automatically calculate the due date based on the vendor's payment terms, include TermsRefListID and omit DueDate. QODBC will apply the term-based calculation.
• BillExpenseLine inserts are separate from the Bill header. Expense line items are inserted via a separate OPENQUERY INSERT into BillExpenseLine before the Bill header. They do not affect due date logic.

Related Articles:

QODBC: Inserting Bills via MS SQL Server and QRemote QODBC: Understanding Payment Terms in QuickBooks Desktop

Keywords: