Troubleshooting - How to use Date() And DateAdd() function in QODBC
Problem Description:
I want to write some select statements on InvoiceLine and SalesReceiptLine that return all records dated WITHIN the past 30 days relative to whatever TODAY is. I'm very familiar with Microsoft SQL syntax and would normally say... WHERE TxnDate >= getdate()-30
How can I reference "30 days ago" using the QODBC driver?
Solutions:
In QODBC, the function CURATE() – Returns the current computer system date as a date value.
For example, for Today, April 18, 2006, when the following query:-
SELECT {fn CURDATE()} as Today, ({fn CURDATE()}-30) as "30 Days Ago", TxnDate, RefNumber, InvoiceLineDesc FROM invoiceline WHERE TxnDate >= ({fn CURDATE()}-30) is run in QODBC Test Tool the results were:
Problem Description:
I need to select only the transactions that occurred in the last 90 days. I used the Access functions Date() And DateAdd() in the Criteria to filter for those transactions, but I am getting the error message "Invalid Procedure Call." Here are the Criteria that I am trying to use:
Between Date() And DateAdd("dd",-91,Date())
What am I doing wrong? Does QODBC have different functions for this?
Solutions:
To write Pass-thru queries for reading and to write QuickBooks® data using QODBC and Microsoft® Access, you must use the proper date format.
You may use Date Macros, but you may also use specifically from and to dates for more flexibility.
This function formats dates with the QODBC format: {d 'YYYY-MM-DD'}. There is no need to remember the form, just the function's name: 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 on 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® to open to using QODBC, open it and ensure you authorized QuickBooks® to communicate with QODBC. 4. Make sure the following references are checked in your Microsoft® Access database: Visual Basic For Applications Microsoft Access 10.0 Object Library Microsoft DAO 3.6 Object Library
To use fncGetChecks, call it from a form or 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: a name for the new query (make sure this doesn't already exist in your database)
a beginning date
an ending date
Your connection string which may or may not be the default offered
Function fncGetChecks() On Error GoTo fncGetChecks_err Dim q As String, Date1 As Date, Date2 As Date q = InputBox("Give your temporary query a name:", "Temporary Pass-Thru Query", "") Date1 = InputBox("Enter start date:", "Start Date", FormatDateTime(Now, vbShortDate)) Date2 = InputBox("Enter end date:", "End Date", FormatDateTime(Now, vbShortDate)) Dim db As DAO.Database, qd As DAO.QueryDef Set db = CurrentDb Set qd = db.CreateQueryDef(q) qd.ReturnsRecords = True qd.Connect = InputBox("Enter connection string:", "", "ODBC;DSN=QuickBooks Data;SERVER=QODBC") 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%'" DoCmd.RunSQL "select * into tbl" & q & " from " & q Set qd = Nothing Set db = Nothing DoCmd.DeleteObject acQuery, q DoCmd.OpenTable "tbl" & q Exit Function fncGetChecks_err: MsgBox Erl & " " & Err.Number & ": " & Err.Description End Function
Also, refer to the following:
How to Use Prompted Date Ranges in MS Access 2007 using Vista
|