Quick Date Formatting for QODBC SQL Queries with QuickBooks® and Microsoft® Access

To write Pass-thru queries for reading and writing QuickBooks® data using QODBC and Microsoft® Access, you must use the proper date format.

You may use Date Macros but you may also use specific from and to dates for more flexibility.

This function formats dates with the QODBC format: {d 'YYYY-MM-DD'}. No need to remember the format, just the name of the function: fncqbDate.


Function

Function fncqbDate(myDate As Date) As String
myDate = Nz(myDate, Now)
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function



Example

You might use fncqbDate to help create an SQL string with VBA from user input dates.

mySQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly',dateFROM = " & fncqbDate(BegDate) & ", dateTO = " & fncqbDate(EndDate) & " where account like '%checking%'"


Put Some Checks into a Table

Try this out and put some checks into a table:
  1. copy and paste fncqbDate (first function above) into a module
  2. copy and paste fncGetChecks (function below) into a module
  3. if you need QuickBooks® open to use QODBC, open it
  4. make sure you authorized QuickBooks® to communicate with QODBC
  5. make sure the following references are checked in your Microsoft® Access database:
    1. Visual Basic For Applications
    2. Microsoft Access 10.0 Object Library
    3. Microsoft DAO 3.6 Object Library


To use fncGetChecks, call it from a form or simply type: fncGetChecks into the immediate window of the Visual Basic Editor.

Change the default connection string if necessary by entering your connection string when prompted.

The function will ask for:
  1. a name for the new query (make sure this doesn't already exist in your database)
  2. a beginning date
  3. an ending date
  4. your connection string which may or may not be the default offered

Function fncGetChecks()
On Error GoTo fncGetChecks_err
100 Dim q As String, Date1 As Date, Date2 As Date
110 q = InputBox("Give your temporary query a name:", "Temporary Pass-Thru Query", "")
120 Date1 = InputBox("Enter start date:", "Start Date", FormatDateTime(Now, vbShortDate))
130 Date2 = InputBox("Enter end date:", "End Date", FormatDateTime(Now, vbShortDate))
140 Dim db As DAO.Database, qd As DAO.QueryDef
150 Set db = CurrentDb
160 Set qd = db.CreateQueryDef(q)
170 qd.ReturnsRecords = True
180 qd.Connect = InputBox("Enter connection string:", "", "ODBC;DSN=QuickBooks Data;SERVER=QODBC")
190 qd.SQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account " & _
"parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly'," & _
"dateFROM = " & fncqbDate(Date1) & ", dateTO = " & fncqbDate(Date2) & _
" where account like '%checking%'"
200 DoCmd.RunSQL "select * into tbl" & q & " from " & q
210 Set qd = Nothing
220 Set db = Nothing
230 DoCmd.DeleteObject acQuery, q
240 DoCmd.OpenTable "tbl" & q
Exit Function
fncGetChecks_err:

MsgBox Erl & " " & Err.Number & ": " & Err.Description
End Function

Immediate Window Test

Type fncGetChecks into the immediate window of the Visual Basic Editor and hit enter.

Give QODBC and QuickBooks® ample time to connect and return results. A table will open containing the records you requested. The temporary query will be deleted. You may rename the table if you wish to keep it in your database.

VBA Access Function fncDeleteQuery Tutorial

Function deletes a query if it exists to use before creating a new query.



Purpose of Code


Microsoft Access returns an error message if the code tries to create a query with a name that already exists. This pair of functions checks to see if a specific query already exists and deletes it if it does.



Explanation


A name is chosen for the query and assigned to a variable (qName).

The variable qName is sent to the function fncDeleteQuery.

From there, qName travels to the function fExistQuery to see if a query with that name exists.

If the function fExistQuery finds a query with the same name, it's value is changed to true. Otherwise, it's value remains false.



Why two functions?


The user may not always wish to delete a query but may sometimes merely want to check whether a specific query exists. That is the reason for two functions:

  • the function fExistQuery returns True if the query exists and False if it does not exist
  • the function fncDeleteQuery deletes the query only if the function fExistQuery returns True


Code #1:


Function fncDeleteQuery(qName As String)
'deletes the query if fExistQuery returns true
If fExistQuery(qName) = True Then DoCmd.DeleteObject acQuery, qName
End Function



Code #2 fExistQuery


Function fExistQuery(qName As String) As Boolean
'Declare variables, db for the database and i as a number of the type integer to enumerate through the queries
Dim db As DAO.Database, i As Integer
'set db as the current database
Set db = CurrentDb
'start the boolean out as False and only change it if Visual Basic finds a matching query
fExistQuery = False
'refresh the database QueryDefs before accessing them
db.QueryDefs.Refresh
'loop through the database QueryDefs looking for the query that matches the'name of the query you passed to the function through the variable qName
For i = 0 To db.QueryDefs.Count - 1
     'compare lower case (LCase) versions of qName and the QueryDef names
     If LCase(qName) = LCase(db.QueryDefs(i).Name) Then
          'name matches so boolean is now true
          fExistQuery = True
          'name matches so exit loop 
          Exit For
     End If
Next i
 'done with db so set to nothing
Set db = Nothing
End Function




Requirements
      Microsoft Access 2002

Microsoft Access Required References
      Visual Basic for Applications
      Microsoft Access Object Library
      Microsoft DAO Object Library

To learn more about Microsoft Access references, see this post:
           
            Microsoft Access References
            http://www.vbquick.com/2011/09/microsoft-access-references_13.html


How to Put the QuickBooks Terms List into a Microsoft Access Table Using QODBC

Places QuickBooks Invoice Terms List into Microsoft Access table using QODBC .



What are Invoice Terms?


The number of days you give a customer to pay an invoice including any service charges and/or discounts. Invoice Terms appear somewhere on the invoice, usually at the bottom.

Example:

2% 15 Net 30 days: if the customer pays within 15 days, they receive a 2% discount but you expect full payment in 30 days.



Where do I find the QuickBooks Terms List?


Select:

Lists from the QuickBooks main menu
Customer & Vendor Profile Lists from the drop-down menu
Terms List from the fly-out menu



Purpose of Code:


This code uses QODBC to extract the Terms List from QuickBooks and puts that list into a Microsoft Access table (included in Microsoft Office XP).



Uses of code:


Used in functions or on forms or reports to place the Terms List into a table for viewing or creating a report or form or for use in a query or code that requires the Terms List.



Code:


Sub TermsList()
'places QuickBooks Terms List into Microsoft Access table

Dim db As DAO.Database, qDef As DAO.QueryDef, strQ As String
'create a new query to extract QuickBooks data via QODBC
'name new query
strQ = "qryTemp"
'function to delete existing queries - find this function here
fncDeleteQuery (strQ)
'assumes you're working in the current database
Set db = CurrentDb
'create the new query with the string variable that equals "qryTemp"
Set qDef = db.CreateQueryDef(strQ)
'make sure to return records
qDef.ReturnsRecords = True
'function to retrieve QODBC connection string - find this function here
qDef.Connect = QODBCConnect
'select all fields from terms table or whatever data you want to extract from it
qDef.SQL = "select * from terms"
'now the query is created and waiting so you are finished with the variables, set them to nothing
Set qDef = Nothing
Set db = Nothing
'declare a variable to hold the table name
Dim strTable As String
'give the table a name
strTable = "tblTermsList"
'turn off warning to replace tblTerms
DoCmd.SetWarnings False
'run SQL to extract the records from the query you made and put them into a table
DoCmd.RunSQL ("select * into " & strTable & " from " & strQ)
'turn warnings back on
DoCmd.SetWarnings True
'open the new table
DoCmd.OpenTable ("tblTermsList")
'delete the new query because the data is now in a table
DoCmd.DeleteObject acQuery, strQ 
End Sub




Requirements:

QODBC Read Version
Microsoft Access 2002
QuickBooks by Intuit


Required Microsoft Access References

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft Office Runtime Library
Microsoft DAO 3.6 Object Library

For more about Microsoft Access References see:

             www.vbquick.com/2011/09/microsoft-access-references_13.html

How to Put QuickBooks Invoice Terms into a Microsoft Access Table

Places QuickBooks® Invoice Terms for Selected Invoices into Microsoft® Access table using QODBC



What are Invoice Terms?


The number of days you give a customer to pay an invoice including any service charges and/or discounts. Invoice Terms appear somewhere on the invoice, usually at the bottom.

Example:

2% 10 Net 30 days: if the customer pays with 10 days, they receive a 2% discount but you expect full payment in 30 days.



Where do I find the QuickBooks Terms when creating invoices?


You may or may not use terms on your customized invoices. QuickBooks invoices like Intuit Product Invoice or Intuit Professional Invoice come with a Terms drop-down box. This is where you select the Terms for each invoice and where you may add new Terms.

You may also select Terms for customers from the Edit Customer window.



Purpose of Code


This code uses QODBC to extract the Terms applied to Specific Invoices from QuickBooks and places those records into a Microsoft Access 2 (included in Microsoft XP) table.



Uses

Used in functions or on forms or reports to place Terms applied to Specific Invoices into a table for viewing or creating a report or form or for use in a query or code that requires those records.

You must limit the invoices you wish to query using one or more parameters.

The code here selects invoices by date but you may also use other parameters such as invoice numbers, customer names, job names, or any other parameters you wish. If you do not use parameters to limit the query, QODBC will query all invoices and could hang for a while.



Code


Sub TermsFromInvoices()
'puts QuickBooks terms from specific invoices into a Microsoft Access table
Dim db As DAO.Database, qDef As DAO.QueryDef, strQ As String
'create a new query to extract QuickBooks data via QODBC
'name new query
strQ = "strQ"
'use function to delete named queries if they exist or you will get an error message that the query already exists - find this function here
fncDeleteQuery (strQ)
'set db to the current database
Set db = CurrentDb
'create a querydef
Set qd=db.createquerydef(strQ)
'set new query to return records
qDef.ReturnsRecords = True
'uses this function to retrieve QODBC connection string - find this function here
qDef.Connect = QODBCConnect 
'select terms and whatever other data you want to extract from the invoice records'be sure to qualify by date, reference number or some factor to limit results or query make take all day'note the QODBC date format
qDef.SQL = "select customerreffullname,termsreflistid,termsreffullname from invoice where txndate>{d '2011-06-01'} and txndate<{d '2011-06-30'}"
'now the query has been created and exists in your database'you may now set the querydef and database to nothing because you are finished with them
Set qDef = Nothing
Set db = Nothing
'declare string variable to hold table name where you will put the query results
Dim strTable As String
'name the table
strTable = "tblTerms"
'turn off warnings to create table DoCmd.SetWarnings False 'run SQL to extract query data and put into the table you just named
DoCmd.RunSQL ("select * into " & strTable & " from " & strQ)
'turn warnings back on
DoCmd.SetWarnings True
'open the table you just made
DoCmd.OpenTable ("tblterms")
 'delete the new query
DoCmd.DeleteObject acQuery, strQ
End Sub

Requirements:

      QODBC Read Version
      Microsoft Access 2002
      QuickBooks by Intuit


Required Microsoft Access References  

      Visual Basic for Applications
      Microsoft Access 10.0 Object Library
      Microsoft Office Runtime Library
      Microsoft DAO 3.6 Object Library

For more about Microsoft Access References see:


      www.vbquick.com/2011/09/microsoft-access-references_13.html




Import QuickBooks Tables: IIF vs. QODBC

Importing Tables: IIF vs. QODBC

The Three Part Tutorial: QuickBooks Tables in Access w/ QODBC Form (www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html) scratches the surface of QODBC's table function. Why not simply export tables using QuickBooks® export feature?

You can, but let's examine each method.

To export the list of accounts from QuickBooks®:
  1. Select Utilities from File menu
  2. Select Export from the Utilities fly-out
  3. Select Lists to IIF files from the Export fly-out




We'll use the Chart of Accounts list, so check that one and hit OK.





Name the file and save it.




  1. Navigate to the folder where you saved the QuickBooks® IIF file.
  2. Right-click the file
  3. Select Open With
  4. Select Excel


With the file open in Excel, Select Save As from the File Menu.



  1. Select Microsoft Excel Workbook as the file type.
  2. Name the file.
  3. Hit Save



Now your folder contains two files named Account. One is the original IIF file and the other the Excel file you just saved.




Next open the database where you wish to use the Account List.
  1. Select Get External Data from the File menu.
  2. Select Link Tables from the Get External Data menu




Select the Accounts.xls file you saved.




Name the new linked table: Accounts.




 Check the box to tell Access the first row contains column headings.



Save the table. It is now linked to the spreadsheet named Accounts.



Open the table in Design View.



Note all the fields in the table. The linked table contains 14 fields.

Note the field names. How definitive are they?


Now open the VB Demo that comes with QODBC. We're going to look at the Accounts table from there.

Select Add New Connection from the Connections menu of the VB Demo.



Select QuickBooks® Data from the Machine Data Source tab and click OK.




Type: sp_columns account and hit the Query button.




You should see something like the screenshot below.

First, notice you now see 29 fields versus the 14 fields from an IIF file. 

Second, notice how much more descriptive the field names are.

Third and perhaps most imporant, notice the first field named ListID. ListID's are not available to you in the QuickBooks® interface or in IIF files.

ListID fields are important for three reasons:
  1. QuickBooks® assigns them
  2. They are unique identifiers
  3. You cannot change them
These three reasons guarantee perfect Microsoft Access primary keys that are unique identifiers AND link to corresponding QuickBooks® records. But that's not all.



I moved the columns in VB Demo to show you another benefit of using QODBC to import tables into Access.

Note the three columns to the right named:
  1. queryable
  2. updateable
  3. insertable
This information is invaluable when creating a database that writes to QuickBooks®.

Take advantage of this information in Access. Programmatically create queries that do not attempt to insert or update field records that are not insertable or updateable.

Do not underestimate the value of programmatically including the updateable and insertable information.

QuickBooks® has in the past changed whether certain fields are insertable or updateable. If you use QODBC and Visual Basic to their fullest potential, your database capabilities will change as QuickBooks® changes its table properties.

Why depend on announcements or luck to find out that a field you previously could not insert or update is now insertable and updateable?

A simple if/then function can omit non-insertable and non-updateable fields from your queries.

Access forms used to collect user input to insert or update QuickBooks® data can be programmed to tell users what fields are insertable/updateable. When QuickBooks® changes, the form can update without any programming or code changes.

Using QODBC to Create a Customized Data Entry Interface for QuickBooks Invoices

Our company enjoyed QuickBooks® invoices for years; however, that changed in 2001 through no fault of Intuit®. We still use QuickBooks® to store invoice lines but we export that data into QuickBooks®  through a user-friendly invoice entry program.

First, the issues and then how we resolved them.


Invoice Unit Name Issues

You may not face the exact same issues; however, your particular needs may result in similar issues.

Invoices consist of invoice lines. Invoice lines consists of units, quantities and prices. The Rural Utilities Services division of USDA (RUS) dictates our unit names. However:
  1. customers sometimes use different names than RUS
  2. customers sometimes use different names than other customers
  3. engineers sometimes use different names than RUS or than the customer for whom they work
  4. foremen often use field terms that differ from RUS, customer or engineer
If you think this is confusing, imagine the person doing the invoicing.


Cross-Referencing Unit Names

The data entry tech was responsible for cross-referencing. This was accomplished from:
  1. memorization
  2. manual cross-referencing
To add to the confusion, RUS changed their unit names in 2001. Not all customers adopted the new names and some of the customers adoped some of the new names but not all of them.

Relying on the data entry tech was no longer an option.




Solving Unit Name Issues

Your needs may differ but the following solutions illustrate some of the possibilities when implementing a customized invoice entry program.

QODBC is an ODBC (Open Database Connection) driver for QuickBooks® . QODBC lets you read and write QuickBooks® data from a database using VBA (Visual Basic for Applications).

Most of the codes on this site use QODBC. Its versatility is only matched by its ease of use. If you know VBA, the hop to QODBC is a short one.

Using QODBC, a new invoice entry program was developed in Microsoft® Access that:
  1. Lets users cross-reference unit names at data-entry time and on-the-fly.
  2. Calculates quantities by a user-input multiplier on-the-fly. For example: Engineers enter wire in number of feet. The user multiplies this by the number of wires in a span. The number of wires in a span varies from job to job and even from sheet to sheet and pole to pole.
  3. Verbally warns the user of possible errors using typical error scenario algorithms. For example, under no circumstance would a span of wire consist of less than ten feet. If the user enters less than ten feet, a warning stops the program and asks if the footage is correct. Similarly, if the user enters an install unit on a removal invoice, a warning notifies the user that the unit class does not match the invoice class.
  4. Automatically fills in repeated fields for user defined groups. For example, each pole may require up to a dozen units. Each unit is one invoice line. Previously, the user typed the pole number on each invoice line. The new program lets the user enter the pole number once. The program then adds the pole number to subsequent invoice lines until the user inputs a new pole number.
  5. Reduces data entry error by allowing the user to visually follow the engineer's work plan as the database reads the data out loud back to the user. This lets the user confirm and correct entries before exporting them into QuickBooks®. Listening to spoken words while reading along is significantly faster than continually looking back and forth between screen and paper.

The Results

This extensibility through QODBC and Microsoft® Access has reduced both data entry time and user error.

One advantage is that units, prices, totals and invoices remain in QuickBooks®, keeping all bookkeeping entries in one application. The database is simply a user-friendly tool for entering invoices faster, easier and specific to company needs.



Meeting Your Specific Needs

Think about your invoicing. Is there a capability QuickBooks® does not provide that you would like to add? If you do not know Visual Basic, you might consider hiring a student or database specialist to create an industry-specific invoice program for you.

If you are short on funds with which to hire a programmer, you may know enough Visual Basic to write your own program in Microsoft® Access using QODBC.

Look over the codes on this site. You may decide you can do-it-yourself like I did and have a customized invoice system for your company.

QODBC ProfitAndLossStandard Exclude Class Part 1

FREE TUTORIAL

Build-it-Yourself with
Copy & Paste Code

You cannot exclude Classes from a Profit and Loss Report in QuickBooks® but you can using QODBC and Microsoft® Access. With a bit of code, you can create the report with the click of a button.

The idea for this tutorial sprung from a request on Experts-Exchange where I participate and offer solutions pro bono for various issues, usually involving QuickBooks® and QODBC.

I never needed to exclude classes from a Profit & Loss Report but evidently this is a common request.

The issue intrigued me enough to write the code from scratch, create the form and offer it as a solution. The recipient was very happy with the results and it solved their problem.

This is a Two-Part Post. Be sure to click continue to go on and finish the database form and create the report.

You can also download the FREE completed Database from My GoogleDocs Page:

docs.google.com/file/d/0B6a0SM5mDWxGZGFmODE2MWYtZTAzOS00N2VlLWFiYzQtNDZhODRiOTZlMDQw/edit?pli=1

My download instructions reside on The Generic Database:

            How to Download & Open My Free Databases 
            www.thegenericdatabase.com/p/how-to-download-google-docs-databases.html


What This Code Does

  1. Creates a Pass-Thru query (sp_report ProfitAndLossStandard) using user input From and To dates
  2. Selects fields from this Pass-Thru query and concatenates the text and label fields as a 3rd field named Description to eliminate null fields and create a join field for the Report query and puts them into Table1
  3. Adds a Class Filter to the Pass-Thru query (sp_report ProfitAndLossStandard) using user selected Classes from the form listbox
  4. Selects the same fields from the 2nd Pass-Thru query as before, again concatenating the text and label fields as a 3rd field named Description to eliminate null fields and create a join field for the Report query and puts them into Table2
  5. Creates a Select query, joining the two tables by the Description field and creates an additional field that subtracts the Table2 amounts from the Table1 amounts.
  6. Opens a Report  for the user to design.
  7. Sets the new report's record source to qryProfitLoss.
The Report's record source (a select query) in Design View:






This code was tested with:
  1. Microsoft® Access 2002
  2. QuickBooks® Premier Contractor 2009
  3. QODBC Desktop Read Write 2009 Version 9.00.00.253



References

You will need the following references checked in Access:




First you need to create a form in Microsoft® Access that looks something like this:


Name the Form Controls

You must name the form controls as follows because the code uses these control names:
  1. TextBox 1: DateFrom
  2. TextBox 2: DateTo
  3. Command Button 1: cmdReloadClasses
  4. Command Button 2: cmdCreateReport
  5. ListBox: lstClass
Fix up the form any way you like. You may use input masks for the date text boxes (wise), adjust the size of the listbox, etc. Do not worry about the listbox columns or source, the code will do that.




Visual Basic Editor


To open the Visual Basic Editor:
  1. Select Modules from the Database Window
  2. Click Design




Double click the form you created from the Project Explorer. If you do not see the Project Explorer, select it from the View menu of the Visual Basic Editor menu bar.






Code

Besides the code below, you will need 3 additional functions. These additional functions reside here on VBQuick and my other site The Generic Database. At the end of this code, the links will appear to the 3 functions you need and you may get them and put them in a new module. You need them to run this code.

Paste this code into the code window of the Visual Basic Editor:


Option Explicit

Private Sub cmdCreateReport_Click()
      'if an error occurs, go to the error routine
100   On Error GoTo cmdCreateReport_Click_err
      'if the user didn't enter values for the dates,     
      'present a message box and exit     
110   If IsNull(DateFrom) = True Then
120       MsgBox "enter a From Date"
130       Exit Sub
140   End If
150   If IsNull(DateTo) = True Then
160       MsgBox "enter a To Date"
170       Exit Sub
180   End If
      'if the user didn't choose any classes to exlude     
      'present a message box and exit
185   If lstClass.ItemsSelected.Count = 0 Then
190      MsgBox "Select at least one Class to exclude."
195      Exit Sub
200   End If
        

      'go to the function that creates the two tables
205   sp_Report_Tables
      'declare variables for the Database, QueryDef and Query Name
210   Dim db As DAO.Database, qd As DAO.QueryDef, q As String
      'name the query
215   q = "qryProfitLoss"
      'function fncDeleteQuery deletes the query if it already exists
220   Call fncDeleteQuery(q)
      'set the database to the current database
230   Set db = CurrentDb
      'create the QueryDef
240   Set qd = db.CreateQueryDef(q)
      'make sure the query will return records
250   qd.ReturnsRecords = True
      'put the SQL statement into the query
260   qd.SQL = "SELECT Table1.text, Table1.label, Table1.amount AS AllClasses,Table2.amount AS SomeClasses, [allclasses]-[someclasses] AS [AllClasses-SomeClasses] FROM Table1 INNER JOIN Table2 ON Table1.Description = Table2.Description;"
      'this is the line to come back to in case an error occurred     
      'so you can set qd and db back to nothing before exiting
cmdCreateReport_Click_exit:
      'set dq and db back to nothing
270   Set qd = Nothing
280   Set db = Nothing
290   subOpenReport
Exit Sub
cmdCreateReport_Click_err: '
'fncWriteError display information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub cmdCreateReport_Click", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo cmdCreateReport_Click_exit
End Sub



Private Sub cmdReloadClasses_Click()
      'if an error occurs go to the error line
100   On Error GoTo cmdReloadClasses_Click_err
      'before reloading classes, set the listbox rowsource to nothing     
      'if you do not do this you will get the "table already in use" error
110   lstClass.RowSource = ""
      'declare variables for the Database,QueryDef and Query Name
120   Dim db As DAO.Database, qd As DAO.QueryDef, q As String
      'name the query
130   q = "qry_temp_Classes"
      'this function deletes the query if it already exists
140   Call fncDeleteQuery(q)
      'set the database to the current database
150   Set db = CurrentDb
      'create the QueryDef
160   Set qd = db.CreateQueryDef(q)
      'the function "QODBCConnect" simply sets the QODBC connection string     
      'you can type out your connection string if you wish
170   qd.Connect = QODBCConnect
      'set the query to return records
180   qd.ReturnsRecords = True
      'put the required fields in the query     
      'you will want at least the ListID and the FullName     
      'you will choose the FullName from the listbox     
      'you will use the ListID in the query
190   qd.SQL = "select listid,fullname from class"
      'set warnings to false because you may be overwriting this table     
      'when you reload the classes     
      'if you do not set the warnings to false, you will get an error     
      'message about the table already existing
200   DoCmd.SetWarnings False
      'put the query results into a table
210   DoCmd.RunSQL "select * into tblClass from " & q
      'set the warnings back to true
220   DoCmd.SetWarnings True
      'set the new class table as the listbox rowsource
230   lstClass.RowSource = "Select * from tblclass order by fullname asc"
      'set the listbox column widths     
      'this is done just so I didn't have to explain how to set them on the form
240   lstClass.ColumnWidths = "0;3"
      'same with the column count
250   lstClass.ColumnCount = 2
      'set the listbox rowsource type to Table/Query
260   lstClass.RowSourceType = "Table/Query"
      'come back to this line after an error occurs so you can set     
      'qd and db back to nothing before exiting
cmdReloadClasses_Click_exit:
      'set qd and db back to nothing
270   Set qd = Nothing
280   Set db = Nothing
      'delete the temporary query
290   Call fncDeleteQuery(q)
Exit Sub
'error line to report error information
cmdReloadClasses_Click_err:
'fncWriteError displays information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub cmdReloadClasses_Click", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo cmdReloadClasses_Click_exit
End Sub


Function ClassSQL() As String
      'if an error occurs, go to the error line
100   On Error GoTo ClassSQL_err
      'declare a variant variable to enumerate through the selected items in the listbox
110   Dim varItm As Variant
      'enumerate through the selected items in the listbox

120      For Each varItm In lstClass.ItemsSelected
            'add each selected item to the string variable ClassSQL           
            'this string will be attached to the second ProfitAndLossStandard query           
            'this is adding the ListIDs of the classes you wish to exclude
130         ClassSQL = ClassSQL & "'" & lstClass.ItemData(varItm) & "',"
140      Next varItm
      'add the parameter string to the list of class ListIDs
150   ClassSQL = "ClassFilterListIDs=" & Left(ClassSQL, Len(ClassSQL) - 1)
   
ClassSQL_exit:
Exit Function
ClassSQL_err:
'fncWriteError displays information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Function ClassSQL", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo ClassSQL_exit
End Function


Private Sub sp_Report_Tables()
      'if an error occurs, go to the error line
100   On Error GoTo sp_Report_Tables_err
      'declare variables for the Database, QueryDef, Query Name and SQL string
110   Dim db As DAO.Database, qd As DAO.QueryDef, q As String, strSQL As String
      'name the query
120   q = "qry_Temp_SP_Report"
      'this function deletes the query if it already exists
130   Call fncDeleteQuery(q)
      'set the db as the current database
140   Set db = CurrentDb
      'create the QueryDef
150   Set qd = db.CreateQueryDef(q)
      'this function sets the QODBC connection string     
      'you may simply type your connection string after the = sign if you wish
      'find this function here
160   qd.Connect = QODBCConnect
      'set the query to return records
170   qd.ReturnsRecords = True
      'create the sql to query QuickBooks using the dates from the form     
      'this is for the first table that includes all classes and null classes     
      'returnrows='All' is important - do not leave it out
180   qd.SQL = "sp_report ProfitAndLossStandard show text,label,amount_1 as amount parameters datefrom=" & fncQBDate(DateFrom) & ",dateto=" & fncQBDate(DateTo) & " ,returnrows='All'"
      'set warnings to false because you will be overwriting this table all the time     
      'if you do not do this you will be notified that the table already exists
190   DoCmd.SetWarnings False
      'run SQL selecting the required fields from the pass thru query and putting them into table1     
      'notice you select text and label and then combine text and label in a third field named Description     
      'some rows of the text and label fields are null which would prevent joining in the report query     
      'adding text and label lets you use the Description field to join the two tables in a query
200   DoCmd.RunSQL "select text,label,text  & label as Description ,amount into Table1 from " & q
      'now you are using the same pass thru query but this time you are limiting     
      'the results with the ClassSQL created earlier
210   If IsNull(ClassSQL) = False Then qd.SQL = qd.SQL & "," & ClassSQL
      'put these query results into a second table
220   DoCmd.RunSQL "select text,label,text  & label as Description ,amount into Table2 from " & q
      'set the warnings back to true
230   DoCmd.SetWarnings True
sp_Report_Tables_exit:
250   Set qd = Nothing
270   Set db = Nothing
      'delete the temporary query
Call fncDeleteQuery(q)
Exit Sub
sp_Report_Tables_err:
'fncWriteError displays information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub sp_Report_Tables", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set qd and db back to nothing before exiting
GoTo sp_Report_Tables_exit
End Sub


Private Sub subOpenReport()
'if an error occurs, go to the error line
On Error GoTo subOpenReport_err
'declare variables for the report
Dim obj As AccessObject, cp As Object, rpName As String
    Set cp = Application.CurrentProject
    ' Search for the report ProfitLoss in the AllReports collection.
100   For Each obj In cp.AllReports
         'if the ProfitLoss report is found, open it
110      If obj.Name = "ProfitLoss" Then
            'open the found report
120         DoCmd.OpenReport "ProfitLoss", acViewPreview
            'go to the exit line to set cp to nothing before exiting
130         GoTo subOpenReport_exit
140      End If
150   Next obj
       'if the report wasn't found, create one      
'declare a variable for a new report
160    Dim rp As Report
170    Set rp = CreateReport
180    DoCmd.Restore
       'put the new report name into the variable for renaming
190    rpName = rp.Name
       'save the open report
200    DoCmd.Save acReport, rp.Name
       'close the report to rename it
210    DoCmd.Close acReport, rp.Name
       'rename the report: ProfitLoss
220    DoCmd.Rename "ProfitLoss", acReport, rpName
       'open the report in design view
230    DoCmd.OpenReport "ProfitLoss", acViewDesign
       'set the record source for the report
240 Reports!ProfitLoss.Report.RecordSource = "qryProfitLoss"
       'save the report with the record source
250 DoCmd.Save acReport, "ProfitLoss"
       'ask user to design the report
260 MsgBox "Please design your report and save it."
'come here after an error occurs to set cp back to nothing before exiting
subOpenReport_exit:
'set cp to nothing before exiting
Set cp = Nothing
Exit Sub
subOpenReport_err:
'fncWriteError display information about the error to the user and writes that information in the
'immediate window for the programmer
Call fncWriteError(Form.Name, "", "Private Sub subOpenReport", Err.Number, Err.Description, Erl, "")
'go back to this line so you can set cp back to nothing before exiting
GoTo subOpenReport_exit
End Sub

QODBC ProfitAndLossStandard Exclude Class Part 2

Continued from...Part 1

This is a Two-Part Post. You needed to complete the steps in the Part 1 before completing the steps outlined in this post.

Other Codes Required

The above code refers to the following functions that you will need.

Create a new module and add these functions to it:

fncDeleteQuery:


QODBCConnect


fncWriteError: 




Command Buttons

Now that the code is installed, you need to set the two command buttons' On Click events.

Move to the report that is in Design View and save it, thereby saving your code. Keep the report in Design View.
  1. Double-click the Reload Classes button
  2. Select the Event tab from the Properites Window.
  3. Scroll down to the On Click event.
  4. Place the cursor in the text box next to On Click
  5. Click on the arrow that appears at the end of the text box
  6. Select [Event Procedure]
  7. Repeat these steps for the Query button.




Change to Form View by right-clicking on the form and selecting Form View.




Click the Reload Classes button to fill the listbox with QuickBooks® classes. This list will not be restricted to active classes but will also include inactive classes. The assumption is that you may wish to create reports for past periods in which classes are no longer active.

Wait for QODBC to connect to QuickBooks®, retrieve the classes, and fill the listbox.




If you try to create the report with no classes selected, you will see this message box:




If you try to create the report without entering the dates, you receive another message box:




With dates entered and classes selected, click the Create Report button.

A new report is created and named: "Profit Loss" and you are asked to design the report.




If you prefer to create the Report from AutoReport: Tabular:
  1. Close the new Profit Loss report and delete it from the Database Window.
  2. Select Reports from the Objects List in the Database Window
  3. Click New Report from the menu bar of the Database Window
  4. Select AutoReport: Tabular in the New Report Wizard
  5. Select qryProfitLoss as the report record source



When the report opens, right-click anywhere on the report and select Save As.




Make sure to name the report ProfitLoss because that is the report name the code uses to open the report.

Fix up the report any way you like, just be sure to save it as ProfitLoss.


Close the report and use the form to re-create it to confirm you saved it with the correct name and all is well.



Confirming Figures

To confirm that this report calculates the Profit & Loss Standard report excluding the classes you selected, go back to QuickBooks® and:

  1. Create a Profit & Loss Standard report using the same dates as your database report
  2. Create a 2nd Profit & Loss Standard report and modify it by selecting Multiple Classes from the Class Filter. Be sure to select the same classes that you selected for your database report.
  3. Subtract the Net Profit from the 2nd (modified) report from the Net Profit of the 1st (NOT modified) report. This amount should match the Net Profit from the AllClasses-SomeClasses column of your database report.
NOTE: If the value of the Net Profit of the 2nd (modified) QuickBooks® report is a negative value, remember, you must subtract and when subtracting a negative value (--) you are really adding the absolute value (+). For example:

Two minus negative one equals three.

2 - (-1) = 3

To confirm this, go to the Immediate Window of the Visual Basic Editor and type:
debug.Print 2-(-1)
The Immediate Window will display the answer: 3

QODBC QuickBooks Customer Table Access Tutorial

What This Code Does

Uses QODBC to import the Customer table from QuickBooks® and put it into a Microsoft® Access Table. This is a reference table that may be refreshed so do not alter or edit this table.



Uses for This Table

The customer table from QuickBooks®  may be linked to any number of tables in Microsoft®  Access to extend your customer information database.

For example, you may wish to:
  1. keep more information for customers in your database than QuickBooks®  accomodates. For example, personal information, birthdays, anniversaries, family members, hobbies, etc. to access when calling customers for a personal touch.
  2. keep sensitive customer information in a secure database
  3. link many records to one customer, for example, ABC Insurance Company may have three departments with seven people that you deal with but you do not wish to add each person to your QuickBooks®  file as a separate customer


Customers and Jobs

The customer table is really the jobs table for contractors or other QuickBooks® users who add jobs to customers. Every job will be listed in the customer table. To list only customers, use a query that limits Sublevels to 0 something like the query below. Instead of using the customer table, you would then use this SQL or a saved query with this SQL as the recordsource for your form or listbox.

SELECT qbCustomers.*
FROM qbCustomers
WHERE (((qbCustomers.Sublevel)=0))
ORDER BY qbCustomers.Name;



How to Link

ListID is a unique identifier for QuickBooks®  tables. Use each customer's ListID as a lookup field in your other database tables.




This Was Tested With

QODBC Desktop Read Write 2009 Version 9.00.00.253
QuickBooks®  Premier Contractor 2009
Microsoft®  Access 2002 SP3



How to Use This Function

Since this is a function, you can call it from any form. You could use a Refresh Customers button on a form to reload the customer table when new customers are added in QuickBooks or customer information changes. Since the ListIDs will always remain the same, the link will always work as long as you use the Customer table ListID as a lookup field in your database tables.

NOTE: If you decide to call this function from multiple forms, you will need a function that closes any OPEN forms using this table or Visual Basic will display an error message that the table cannot be deleted because it is in use.

If you only call this function from one form that uses this table as a recordsource (including controls), you must set any recordsources using this table to "" before calling the function and then set them back after the table is refreshed.

You may wish to place the Reload Customers button on a switchboard that does not use the table as a recordsource and then call a function that closes all open forms, reloads the customer table, and then opens the closed forms back up.



Microsoft Access References

You will need the following references checked from the Visual Basic Editor:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object





Code

Copy this code and paste it into a module in the Visual Basic Editor.

Function ReloadCustomers()
        'if an error occurs, go to the error line that writes error information to       
        'a message box for the user and the immediate window for the programmer
On Error GoTo ReloadCustomers_err
        'declare variables for database, QueryDef and the query name
10      Dim db As DAO.Database, qDef As DAO.QueryDef, qName As String
        'assign the name of the query to the string variable
20      qName = "qryPT_Customers"
        'call a function that deletes the temporary query if it already exists       
        'this query first checks to see if one with the same name exists and if it does, deletes it       
        'if you try to delete a query that doesn't exist, Visual Basic will present       
        'an error message telling you it cannot find the query
30      Call fncDeleteQuery(qName)
        'set the database - assumes you are working in the current database
40      Set db = CurrentDb
        'create the query using the name you assigned above
50      Set qDef = db.CreateQueryDef(qName)
        'QODBCConnect is a function that sets the QODBC connection string       
        'this avoids having to remember it every time you need to use it       
        'you can simply write "qDef.Connect = " and then type out your connection string if you prefer
60      qDef.Connect = QODBCConnect
        'set the query to return records
70      qDef.ReturnsRecords = True
        'create the sql string and put it into the query       
        'the more fields you select, the longer the query will take       
        'you are better off selecting only required fields       
        'limiting the returned records is best done at the query level       
        'rather than limiting them when you write the sql that puts the records into the table       
        'QODBC takes longer to extract records from QuickBooks than       
        'Microsft Access takes to extract records from the Pass Thru query       
        'and put them into a table
80      qDef.sql = "select * from Customer"
        'before you put the query records into a table, you may       
        'use a function to delete the table if it exists
90      Call fncDeleteTable("tblCustomers")
        'Turn off warnings so you don't get one for overwriting an existing table       
        'This table will be frequently overwritten as well as the query that       
        'supplies the data to it because whenever you add a new price or price level       
        'in your QuickBooks file, you will need to refresh the database table       
        'you could simply create the pass thru query but if the user deletes it       
        'will they know how to recreate it? best to create it programmatically
100     DoCmd.SetWarnings False
        'since you limited the pass thru query to only records that you require       
        'go ahead and select them all for putting into the price level table       
        'here the user will not know what is going on       
        'you may do as I do and open a form telling the user you are extracting       
        'data from QuickBooks and then close the form when you are finished
110     DoCmd.RunSQL "select *  into qbCustomers from " & qName
        'delete the pass thru query because you don't need it now
120     DoCmd.DeleteObject acQuery, qName
        'turn warnings abck on
130     DoCmd.SetWarnings True
        'present the user with a message box to signal the price levels are refreshed
140     MsgBox "Finished."
        'use this line to return to from the error routine so if an error occurs       
        'qDef and db will be set to nothing
ReloadCustomers_exit:
        'you are finished with qDef and db so set them to nothing       
        'if you do not set db to nothing, you may find Access running       
        'in your task manager even after you close the database
150     Set qDef = Nothing
160     Set db = Nothing
        'exit the function - are finished
Exit Function
        'at the beginning of this function, you instructed Visual Basic to come here       
        'if an error occurs       
        'fncWriteError presents the user with error information in a message box       
        'and also writes the same error information in the immediate window       
        'you may also write fncWriteError to put the error information into a table
ReloadCustomers_err:
Call fncWriteError("", "Functions Customers", "Function ReloadCustomers", Err.Number, Err.Description, Erl, "")
        'after writing the error information, return to the exit line to set qDef and db to nothing before
        'exiting the function
GoTo ReloadCustomers_exit
End Function


Additional Functions Required

You may already have these in your database if you have used other codes from VBQuick or The Generic Database. If you do not have them, you need to put them into a Module. The above code will not work without them. You need only copy and paste them. Each page provides a link to the code tutorial if you want to understand what they do and how they do it.


fncDeleteQuery:

fncDeleteTable:

fncWriteError:

QODBCConnect





Create the Customer Table

With your QuickBooks file open (if required) and all the code installed, simply type Reload Customers into the Immediate Window of the Visual Basic Editor and hit Enter like this:



Then move back to the Database Window to see the new table: qbCustomers.

Using a prefix to denote QuickBooks® tables helps avoid overwriting or deleting permanent tables that you create in Microsoft® Access. This code uses the prefix qb but you may change that in the code to anything you like. Remember to be consistent. Choose one prefix that you will use for tables imported from QuickBooks® that you will continually overwrite. You may use a prefix like temp, tmp, QODBC, etc.




Customers and Jobs or Just Customers?

Remember, this code queries the QuickBooks customer table using QODBC and puts it into a Microsoft Access table. The QuickBooks customer table contains customers and jobs. You may use the code as written and select only customer using a select query in your database as follows:

SELECT qbCustomers.*
FROM qbCustomers
WHERE (((qbCustomers.Sublevel)=0))
ORDER BY qbCustomers.Name;
However, you may also change the code only query the customers if you never plan to use jobs in your database. This will save time at the Pass Thru query level. To use QODBC to query only customers, replace line 80 with:

80      qDef.sql = "SELECT * FROM Customer where sublevel=0"



Asterisk

The asterisk includes all the customer fields in the query. To further speed up the QODBC query, you may select only the fields you require in your database.

To design a form that shows you the fields for any QuickBooks table available through QODBC, see this Three-Part Tutorial:

            QuickBooks Tables in Access w/ QODBC Form
            www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html