Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • 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.
Module1.txt
0
stlinae
Asked:
stlinae
1 Solution
 
Nick67Commented:
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
0
 
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.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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