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 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


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

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.


Popular Posts