Knowledgebase
[QODBC-Desktop] Troubleshooting - How to use Date() And DateAdd() function in QODBC
Posted by Jack - QODBC Support on 13 March 2015 10:05 AM

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 

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).