RSS Feed
Knowledgebase : TroubleShooting > Syntax
TROUBLESHOOTING: HOW TO QUERY INVOICE LINES WHERE TXNDATE=YESTERDAY USING CURDATE PROBLEM DESCRIPTION 1 I am using Excel to pull info from the InvoiceLine QB table. This triggers the 'Microsoft Query' program which looks like an access design grid. ...
TROUBLESHOOTING: HOW TO CREATE MULTIPLE INVOICES WITH MULTIPLE LINE ITEMS USING ACCESS 2003 AND QODBC PROBLEM DESCRIPTION 1 I use MS Access 2003 to generate calculated totals for invoices that I need to post into QB. Using an Append Query I have ...
TWO EXAMPLES OF WRITING DEPOSITS INSTRUCTIONS NOTE: Deposit inserts are done only to the DEPOSITLINE table. The Deposit table is a header table and cannot be inserted into without cached DepositLine transactions. TROUBLESHOOTING NOTE If you encoun...
TROUBLESHOOTING: "ERROR PARSING COMPLETE XML RETURN STRING (8)" IN SOME INSERT STATEMENTS PROBLEM DESCRIPTION Someone might get an error "[QODBC]Error parsing complete XML return string. " when running some inserts queries such as PurchaseOrders, Invoic...
Problem With Date Format TROUBLESHOOTING: PROBLEMS WITH DATE FORMAT PROBLEM DESCRIPTION 1: When want to filter record by date, input a query like below: select top 1 * from DepositLine WHERE TimeCreated < '2011-06-06' ORDER BY TimeCreated DESC But...
TROUBLESHOOTING: QUICKBOOKS ERROR: TRANSACTION NOT IN BALANCE PROBLEM DESCRIPTION The QODBC report Error: 3180-There was an error when saving a General Journal transaction. QuickBooks error message: Transaction not in balance. When trying to do some...
Why Special Character Turns into
QODBC can't mark fund transfer as cleared TROUBLESHOOTING: CAN'T MARK FUND TRANSFER AS "CLEARED" PROBLEM DESCRIPTION: Want to use QODBC to mark a fund transfer transaction as "Cleared", but don't know how to do it. Try to modify field TransactionPai...
Item Sites table contains no data TROUBLESHOOTING: COULD NOT FIND DATA IN ITEMSITES TABLE QUICKBOOKS PROBLEM DESCRIPTION: When referring to Intuit SDK OSR, a subject named Item Site can be found. It stores the quantities for each item by sites. But I...
ClearedStatus Table problem TROUBLESHOOTING: COULD NOT MARK TRANSACTION AS CLEARED IN CLEAREDSTATUS TABLE PROBLEM DESCRIPTION 1: Try to Mark a transfer type transaction as cleared in ClearedStatus table via the statement: INSERT INTO ClearedStatus...
TROUBLESHOOTING: EXPECTED LEXICAL ELEMENT NOT FOUND PROBLEM DESCRIPTION  When trying to execute a query statement I get the error message " EXPECTED LEXICAL ELEMENT NOT FOUND " SOLUTIONS It seems to be the issue in the SQL Statement. Please check all...
TROUBLESHOOTING: UPDATING JOURNALENTRY RECORDS VIA QODBC SHOWS ERROR, TRANSACTION NOT IN BALANCE INSTRUCTION: Under QuickBooks when saving a Journal Entry record, the Debit total should match with Credit total. Even thou you are not changing the amou...
TROUBLESHOOTING: QODBC HANGS WHEN TRYING TO EXECUTE QUERY OR VIEW TABLES PROBLEM DESCRIPTION: When executing a simple query like: SELECT * FROM Invoice Application and QuickBooks will both get hang, and it Does Not Respond. Then After, the applic...
PROBLEM DESCRIPTION: I am attempting to apply payments to invoices I have also determined the TxnID of the Invoice I want to apply payment to and used it in the SQL code specified on How to apply payment to an invoice [http://support.flexquarters.com/esu...
PROBLEM DESCRIPTION: How can I add blank InvoiceLine in QuickBooks using QODBC? SOLUTION: There are empty Invoice line records in QuickBooks, and QODBC can view it too, but you may not be able to create an empty InvoiceLine in QODBC. You cannot crea...
PROBLEM DESCRIPTION 1: I'm working in PHP and using the VB Demo 32. I am trying to Insert value into the Customer table. But I am getting below error: EXPECTED LEXICAL ELEMENT NOT FOUND: My insert statement is: Insert into Customer ('CompanyN...
HOW TO ADD ADDITIONAL QUOTE (') IN INSERT STATEMENT USING QODBC PROBLEM DESCRIPTION: I have exported customer list from QB and Now I wanted to insert these using insert query. But some of them are failing because of having the apostrophe ('). How to de...
HOW TO CREATE AN ITEM INVENTORY ASSEMBLY THROUGH MS ACCESS USING QODBC AN EXAMPLE OF CREATING ONE ITEMINVENTORYASSEMBLY WITH TWO LINES First of all please create new MS Access database & link ItemInventoryAssembly, ItemInventoryAssemblyLine table. Pl...
HOW CAN I FIND QUICKBOOKS DELETED RECORD INFORMATION FROM QODBC? PROBLEM DESCRIPTION: Is there a stored procedure that provides all deleted transactions? I.e. Voided/Deleted Transactions Detail? For Example: If an Invoice gets deleted from QuickB...
TROUBLESHOOTING: SP_REPORT PROFITANDLOSSBUDGETPERFORMANCE IS RETURNING NO ROWS. PROBLEM DESCRIPTION 1: I am running ProfitAndLossBudgetPerformance through MS Query & It is returning no rows. But using QuickBooks UI I am able to get records from ProfitA...
PROBLEM DESCRIPTION: When inserting an invoice via a looping SQL procedure, I have invoice lines (InvoiceLine table) items that can potentially be assigned to varying classes. Using the ClassRefListID field, I class the lines accordingly, and do not addr...
TROUBLESHOOTING - CANNOT CREATE NEW TABLE USING QODBC / CANNOT ADD A NEW FIELD TO QODBC TABLE PROBLEM DESCRIPTION: I'm trying to create a new table with VB Demo. Can you send me a sample query to do this? SOLUTIONS: QODBC is an ODBC driver. Please...
TROUBLESHOOTING - ERROR [42S00] [QODBC] INSERT VALUE MUST BE A SIMPLE VALUE PROBLEM DESCRIPTION 1: We have a customer trying to import a credit memo from our database into QuickBooks using a vb.net program. They are getting the following error: ERROR [4...
TROUBLESHOOTING: ERROR 3250 - THIS FEATURE IS NOT ENABLED OR NOT AVAILABLE IN THIS VERSION OF QUICKBOOKS. PROBLEM DESCRIPTION: I am getting error "Error 3250 - This feature is not enabled or not available in this version of QuickBooks." while I am tryi...
TROUBLESHOOTING - INTERNAL ERROR WHEN PROCESSING THE QBXML REQUEST PROBLEM DESCRIPTION: I have QuickBooks 2014 version & I am using QODBC latest version. I have a problem with querying customer table using QODBC. QODBC driver consistently gets stuck o...
TROUBLESHOOTING - HOW TO GET ALT.EMAIL1,2 & CC EMAIL FIELDS FROM CUSTOMER TABLE PROBLEM DESCRIPTION: How do I access the alternate email address 1 and 2 and the cc email fields? I don't see them at the customer table. SOLUTIONS: You can get CC Email ...
TROUBLESHOOTING - MS ACCESS CRASHES HARD ON DATE QUERY PROBLEM DESCRIPTION: I've linked all the QODBC tables into an Access 2010 DB. I have created table subset queries on the original tables and would like to do selects based on date ranges - other thi...
TROUBLESHOOTING - CANNOT USE ALIAS IN MS QUERY PROBLEM DESCRIPTION: I am getting the below error message when trying to SELECT data fields AS Alias, the statement runs fine otherwise. [sql syntax error] Expected lexical element not found:= Please se...
TROUBLESHOOTING - GETTING [QODBC] NOT SUPPORTED ERROR WHEN TRY TO MODIFY TXNDELETED TABLE PROBLEM DESCRIPTION: I am trying to modify the TxnDeleted table from QuickBooks in QODBC but getting "[QODBC] Not Supported" error. SOLUTIONS: The TxnDeleted t...
TROUBLESHOOTING - HOW TO CREATE BLANK INVOICE IN QUICKBOOKS ONLINE USING QODBC PROBLEM DESCRIPTION: How do I create a note (or a blank line) on the invoice? If I do it the same way that I do it using QODBC for QuickBooks I get an error : Error sending...
TROUBLESHOOTING - INVOICE REFNUMBER FAQ PROBLEM DESCRIPTION: I have invoice number (RefNumber) from my own table outside of QuickBooks. But if QuickBooks user creates invoices directly in QuickBooks then QuickBooks keeps new incrementing invoices autom...
TROUBLESHOOTING - HOW TO USE DATE() AND DATEADD() FUNCTION IN QODBC PROBLEM DESCRIPTION: I want to write some select statements on both InvoiceLine and SalesReceiptLine that returns all records dated WITHIN the past 30 days, relative to whatever TODAY ...
TROUBLESHOOTING - HOW TO SELECT RECORD WHEN VALUE IS NULL PROBLEM DESCRIPTION: I am trying to run a report which returns ItemInventory where LastReceived is NULL I have tried in vain to accomplish this. My question is what syntax do you use to select a...
PROBLEM DESCRIPTION: How to use parameter in OPENQUERY SOLUTIONS: OPENQUERY does not accept variables for its arguments. You need to Pass Basic Values as below example: SELECT QUERY: DECLARE @TSQL VARCHAR(8000), @ID VARCHAR(25) SELECT @ID = '19...
TROUBLESHOOTING - GETTING MULTIPLE-STEP OLE DB OPERATION GENERATED ERRORS IN SQL SERVER PROBLEM DESCRIPTION: Trying to insert into SalesOrderLine and getting a 'Multiple-step' error. INSERT INTO QBREMOTE...SalesOrderLine (CustomerRefListID, TemplateR...
TROUBLESHOOTING - QREMOTE DOES NOT CONSIDER FQSAVETOCACHE WITH WORKING WITH ODBCCOMMAND & PARAMETERS PROBLEM DESCRIPTION: QRemote Does not consider FQSaveToCache with working with OdbcCommand & Parameters I have an application that creates Sales Order...
DIFFERENCE BETWEEN QODBC FOR QUICKBOOKS DESKTOP AND ONLINE. TABLE DIFFERENCES: Number of Tables available in QODBC DESKTOP QODBC ONLINE 150 83 List of Tables available in QODBC Desktop [http://doc.qodbc.com/qodbc/usa/TableList.php?catego...
TROUBLESHOOTING - HOW TO GET BANK RECONCILIATION DETAILS USING QODBC PROBLEM DESCRIPTION: How to get Bank Reconciliation details using QODBC I've not had success identifying any field/flag that identifies the Bank Reconciled status. Is this informatio...
EXAMPLES OF CREATING INVOICE USING QREMOTE FROM SQL SERVER NOTE: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final ...
TROUBLESHOOTING - ERROR WHILE INSERTING BILL PROBLEM DESCRIPTION 1: I am following the below steps and getting the error. 1.First, we have inserted the records in Bill & BillExpenseLine table and records got inserted successfully. 2.Second, we are in...
TROUBLESHOOTING - GETTING QODBC NOT SUPPORTED ERROR WHILE INSERTING INVOICE PROBLEM DESCRIPTION: I am trying to insert Invoice, But I am getting below error: [QODBC] Not supported (#10003) I am using below SQL statements: INSERT INTO "InvoiceLine...
TROUBLESHOOTING - HOW TO GIVE REFUND TO EXISTING CREDITMEMO USING QODBC PROBLEM DESCRIPTION: How can I apply a refund check to an existing credit memo using QODBC? In QuickBooks, I can refund by opening the Create Credit Memos/Refunds dialogue box usin...
TROUBLESHOOTING - HOW TO LOCATE DATA FOUND IN QUICKBOOKS BY TABLE AND FIELD NAME INSIDE QODBC PROBLEM DESCRIPTION: I have a question about How to locate data found in QuickBooks by table and field name inside the QODBC system. SOLUTIONS: The fastest ...
GENERAL CONNECTION STRING INSTRUCTIONS The most difficult part of using QRemote in programming is the connection string. The simplest form includes a reference to a DSN. A system DSN called "QuickBooks Data QRemote" for 32-bit application & "QuickBooks...
TROUBLESHOOTING - HOW TO GET VBA TO EXECUTE TWO STATEMENTS AT THE SAME TIME PROBLEM DESCRIPTION: We are using a Visual Basic app which works with an SQL Server database to write data to QB through QODBC. We are executing two SQL statements as described...
TROUBLESHOOTING - QODBC CONNECTION TO TALEND OPEN STUDIO PROBLEM DESCRIPTION: QuickBooks Enterprise comes with a tool called QODBC that enables connecting to a QB database to read and write data. I have installed it, and I can successfully see QB data ...
TROUBLESHOOTING - QODBC DRIVER DROPPING A FEW TRANSACTIONS ON INSERT TO INVOICELINES PROBLEM DESCRIPTION: We seem to be having an issue with an insert into the InvoiceLines table dropping a few transactions. We need to create about 1000 invoices per m...
HOW TO GET CONVERSION RATIO INFORMATION FROM UNITOFMEASURESETRELATEDUNIT TABLE IN QODBC Conversion ratio information is stored in a table called UnitOfMeasureSetRelatedUnit. PROBLEM: How to get Conversion Ratio information from UnitOfMeasureSetRelated...
HOW TO RETRIEVE CHARACTER FIELDS WHICH ARE RETURNED AS NULL IN SSIS USING QODBC PROBLEM: Using SQL Server Integration Service (SSIS) and QODBC, All character fields are being returned as NULL. SOLUTION: Please use function CONVERT to convert the dat...
PROBLEM DESCRIPTION: How to insert employee time information from MS Excel time spreadsheet into QuickBooks using QODBC SOLUTION: You cannot perform insert/update operation directly in MS Excel because Excel is just exporting table in the spreadshee...
TROUBLESHOOTING - "CANNOT UPDATE TABLE ITEMINVENTORY ERROR 10004" PROBLEM DESCRIPTION: I am trying to run an update on the Name field in ItemInventory. I've checked to see if it's updateable and it is. I get "Cannot update table ItemInventory Error 1...
WHAT IS JUMPIN TYPE? PROBLEM DESCRIPTION: I am looking for definition and usage of "JUMPIN TYPE." SOLUTION: In QODBC JUMPIN provides relationship details with other table and also provides information about value comes from another table. Please ...
TROUBLESHOOTING - GETTING UNEXPECTED EXTRA TOKEN ERROR IN DATE FIELD QUERY PROBLEM DESCRIPTION: I am trying to query the Transaction table based on the TxnDate field. I tried the below code. OdbcCommand SourceCmd = new OdbcCommand("Select * from Tra...
TROUBLESHOOTING - [QODBC] COLUMN NOT FOUND REORDERBUILDPOINT PROBLEM DESCRIPTION: We just upgraded to v16 on both QB and QODBC and now refreshes from Excel aren't working. We're getting the message "[QODBC] Column not found ReorderBuildPoint" SOLUTIO...
TROUBLESHOOTING - [QODBC] INVALID OPERAND FOR OPERATOR: PROBLEM DESCRIPTION: I am getting the error "Invalid operand for operator: " while trying to insert an invoice line item: INSERT INTO InvoiceLine ( CustomerRefListID, T...
TROUBLESHOOTING - INVOICE LINE RATE IS NOT LOADING INTO QUICKBOOKS ONLINE PROBLEM DESCRIPTION: The InvoiceLineRate value passed in the insert query to create the InvoiceLine record is not being loaded into QBO. Below are the two queries I am using to...
TROUBLESHOOTING - HOW DO I LIST THE SUBTOTAL OF INVOICES WITHIN DATE RANGE PROBLEM DESCRIPTION: I would like to do is list find the subtotal of all Invoices by a customer for not only that particular customer but all jobs assigned to that customer, wi...
TROUBLESHOOTING - EXPENSES WRITTEN TO BILLEXPENSELINE ARE NOT SHOWING AS BILLABLE WHEN VIEWED IN QUICKBOOKS PROBLEM DESCRIPTION: The field ExpenseLineBillableStatus contains the status of the BillExpenseLine and using the following stored procedure "s...
TROUBLESHOOTING - HOW CAN I CHANGE THE PRICING AND QUANTITY OF ITEMS IN AN ITEMGROUP? PROBLEM DESCRIPTION: How can I change the price of each individual item in a group? Is it possible? If yes please give me an example. SOLUTION: The price of an it...
TROUBLESHOOTING - HOW CAN I GET ALL THE RECORDS FOR A SPECIFIED YEAR PROBLEM DESCRIPTION: I need to compute a Year to Date total paid using the Invoice table. I'm working in MS-Access and need to run the following statement: SELECT SUM(Subtotal) FRO...
TROUBLESHOOTING - HOW CAN I GET DAILY BILL PAYMENT INFORMATION? PROBLEM DESCRIPTION: How can I get daily payment bill information? And How can I get daily Bill Payment information using QODBC, and How can I get these details? SOLUTION: You can use ...
TROUBLESHOOTING - HOW DO I DELETE ORPHANED INVOICELINE RECORDS? PROBLEM DESCRIPTION: The InvoiceLine table contains orphan records (the parent invoice record no longer exists). I tried: DELETE FROM Invoice WHERE TxnID = 'xxxx-xxxxxxxxx' the TxnID is...
TROUBLESHOOTING - HOW DO I GET TIMEFRAME INFORMATION - SALES BY HOUR REPORTING USING QODBC PROBLEM DESCRIPTION: This is a no technical question but one I need to answer myself before jumping into QODBC. I'm considering start working with QODBC to get ...
TROUBLESHOOTING - HOW DO I VOID AN INVOICE? PROBLEM DESCRIPTION: How do I void an Invoice? And How do I query for invoices that have been voided? I don't see any kind of indicator in the Invoice table. SOLUTION: You can use SP_VOID just like you wo...
TROUBLESHOOTING - HOW TO STOP ANY QUERY ON VB DEMO APPLICATION PROBLEM DESCRIPTION: How to Stop any query on VBDEMO when it takes time. How to convert the date to QuickBooks date. I want to insert TxnDate in the ReceivePayment table, but it pops u...
HOW TO IMPORT DATA TO QUICKBOOKS THROUGH CSV USING QODBC NOTE: QODBC does not support direct import, But you can write VBA code which reads a CSV / Excel file & generates insert statements. There are two ways to Import data to QuickBooks through CSV u...
TROUBLESHOOTING - BILLABLE PAYROLL - NEED TO DISPLAY MORE FIELDS ON INVOICE PROBLEM DESCRIPTION: We need to bill all payroll costs to various customers. Payroll costs are defined as all of the following: 1. Employee's gross salary 2. Company share ...
TROUBLESHOOTING - CORRELATED SUBQUERIES IN UPDATE STATEMENT PROBLEM DESCRIPTION: I would like to update the salesrep field in my existing invoices. I would like to use the salesrep value that is stored in the Customer file. I would normally do this in...
TROUBLESHOOTING - HOW TO EXTRACT PRINTED CHECKS INFORMATION USING QODBC PROBLEM DESCRIPTION: We are involved in a new project with our bank involving a 'Positive Payment' solution that requires I send them a file with the check summary data each time ...
TROUBLESHOOTING - HOW CAN I GET LAST ITEMS SOLD TO CUSTOMER DETAILS PROBLEM DESCRIPTION: I was able to create a report using a customer set with the customer as the parent and InvoiceLine as the child giving me items ordered in the past. My problem ...
TROUBLESHOOTING - HOW CAN I USE WILDCARD (*) WITH CRYSTAL REPORT AND SP_REPORT PROBLEM DESCRIPTION: I am using the following sql statement: sp_report JobProfitabilityDetail show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActu...
TROUBLESHOOTING - HOW DO I GET TABLE RECORD COUNT FASTEST WAY USING QODBC PROBLEM DESCRIPTION: What is the fastest way to get the number of records from a table? SOLUTION: QODBC would need to scan the whole table to get the record or row count. So ...
TROUBLESHOOTING - ODBC DRIVER DOESN'T SUPPORT THIS STATEMENT. UPDATING EXISTING RECORD PROBLEM DESCRIPTION: I'm trying to programmatically make Inventory Items In-Active after a sale or Active after voiding a sale. I would have thought that the foll...
TROUBLESHOOTING - HOW TO USE BETWEEN IN QODBC PROBLEM DESCRIPTION: I need to use Between, in range of dates to return new customer in one period, I try this: SELECT FullName FROM Customer Where TimeCreated Between {d'2007-12-18'} AND {d'2007-12-19'...
TROUBLESHOOTING - UPGRADING TO QUICKBOOKS AND QODBC PROBLEM DESCRIPTION: I have a custom program written in Access 2003 communicating with QuickBooks 2013 using QODBC 13 which works fine. Will upgrading to latest QuickBooks and QODBC require any code ...
TROUBLESHOOTING - HOW DO I REMOVE SUBTOTALS AND BLANK LINES IN SP_REPORT PROBLEM DESCRIPTION: I'm trying to link a general ledger report in Excel. I've created the sp_report SQL query in Microsoft Query just fine, but the data that it returns contains...
TROUBLESHOOTING - GETTING ERROR "INDEX WAS OUTSIDE THE BOUNDS OF THE ARRAY." WHEN USING IMPORT/EXPORT TOOL IN SQL SERVER PROBLEM DESCRIPTION 1: I just recently installed QODBC to try to get data out of a QuickBooks company file but I get this error: "...
TROUBLESHOOTING - HOW DO I UPDATE CUSTOMER CUSTOM FIELDS PROBLEM DESCRIPTION: How do I update Customer custom fields? SOLUTION: You can update Customer custom fields using the update query. For Example: Update Customer Set CustomFieldContract='QO...
TROUBLESHOOTING - TRYING TO UPDATE ITEMINVENTORYASSEMBLYLINE TABLE PROBLEM DESCRIPTION: I am using MS Access connected to QuickBooks via QODBC. I am using queries in MS Access to load/synchronize Inventory data from my master inventory list, which is ...
TROUBLESHOOTING - HOW DO I SEE BACKORDER ITEMS THAT HAVE BEEN INVOICED BUT NOT SHIPPED PROBLEM DESCRIPTION: Could someone kindly provide me with a sample, or is there a stored procedure that would allow me to only pull out back ordered invoices from t...
SAMPLE CODE FOR C++ WITH QODBC #include #include #include #include #include #include #include #include using namespace std; void ConvertTimeStampStructToString(TIMEST...
TROUBLESHOOTING - QODBC RECORD SET ISSUE PROBLEM DESCRIPTION: I am using below code it is returning records & giving me the correct result. But If I change query from "SELECT Name FROM customer" to "SELECT * FROM customer" it does not return any recor...
HOW TO IMPORT QUICKBOOKS DATA TO SQL SERVER DATABASE (LOCAL TABLE) First, you need to create Linked Server in SQL Server. For 32-bit SQL Server. Please refer How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote [http://suppor...
TROUBLESHOOTING - GETTING INCOMPLETE DATA WHEN RUNNING PROFITANDLOSSBUDGETVSACTUAL REPORT PROBLEM DESCRIPTION: Good Day To You. Using This Sp_report And Having Some Problem With The Column Layout. Command Text Is As Follows: Sp_report Profitandlossbud...
TROUBLESHOOTING - UPDATE FROM A SELECT STATEMENT NOT WORKING PROBLEM DESCRIPTION: I am trying to update records through the select statement. But it is not working. UPDATE Customer SET CustomFieldProjectNumber = i.JobNumber FROM ( SELECT ListID, {fn...
HOW TO USE POWERSHELL WITH QODBC Download Sample [http://support.flexquarters.com/esupport/newimages/PowerShell/DisplaySQL.zip] SAMPLE CODE FOR USING QODBC DSN: $connectstring = "DSN=QuickBooks Data;" $sql = "Select TOP 10 Name from Customer" $conn ...