• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

allowing for user to enter dates in a pass-through qry

The setup: using QuickBooks and it's 'qODBC' driver, from a pass-through qry, I need to pull a stored procedure query. The SP queries usually have a parameter [DateMacro] that can use things like 'todays date'  'All' of 'This year to Date' amongst others. However, you can also use [DateFrom] and [DateTo] parameter. The catch is that you MUST have the dates entered before you can run the qry.

I need reports that can have changing dates, therefore need the [DateFrom] and [DateTo] parameter. I understand how to use a form for the dates, but that doesn't work when doing this. I have to have the dates in the query before the query can be 'touched' I have included a sample of a different forums suggestion, and while I understand the idea, I don't get the process. The incorporation of VBA into Access is something I just don't have a grip on.

Please let me know if I need to clarify anything.
1 Solution
The idea is fairly straight-forward.
Your pass-through needs to be perfectly formatted before it will work.
Here's my function that does it, not for quickbooks but the idea is the same
Private Function FormatAPAssthrough()
Dim db As Database
Dim qdf As DAO.QueryDef
Dim rs As Recordset
Dim strRecordSource As String
'how this works:
'On the SQL db there is a stored procedure spCertResultsReport that needs a long parameter
'In the mdb there is a passthrough query that has an ODBC connection string and returns records = true
'The text in the passthrough query matters little, it could even be blank. It gets replaced
'by the code below, then executed and the records populate the report.
Set db = CurrentDb
Set qdf = db.QueryDefs("pthrSpInvoiceSummary") 'this is the pass-through
strRecordSource = "Exec SpInvoiceSummary " & Me.JobID 'Exec SPInvoiceSummary is the SP on the server and me.JobID is the parameter
qdf.SQL = strRecordSource 'this changes the text of the query to be what I need
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges) 'this executes it

'now I do stuff
End Function

Open in new window

For you, you want to pass dates.
If the backend is like SQL server the syntax of the important stuff will be

strRecordSource = "Exec SpInvoiceSummary " & Chr(39) & forms!frmSomeform!txtMyStartdate & chr(39) & ", " & & Chr(39) & forms!frmSomeform!txtMyEnddate & chr(39)

SQL server takes dates as string literals enclosed by single quotes (')
Chr(39) is a single quote
That keeps me from having '" and "' crap all over, getting confused and having a debugger's nightmare.

First build a pass-through that you manully put values into that works.
Then post it, along with your form and control names and we'll beat them with a stick til they play nice
stlinaeAuthor Commented:
Thanks Nick. I haven't had a chance to try this out as life had gotten in the way. However, I am cosing it with points to satisfy the abandon question status.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now