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

Posted on 2011-05-12
Last Modified: 2012-06-27
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.
Question by:stlinae
    LVL 26

    Accepted 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
    LVL 11

    Author Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now