CODEWITHSUNDEEP
ms-accessvbareportdatasource
SteveMap
SteveMap

Reputation: 1

Trying to pass parameters to an MS Access query

I am trying to write a MS Access report based on a query that I can programically put the a date range in using VBA. I set up the query with Between [StartDate] and [EndDate] as the criteria. Then I have the following code on a form:

Private Sub AutoReport1_Click()
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("MixByRangeA")

qdf.Parameters("StartDate").Value = #10/2/2014#
qdf.Parameters("EndDate").Value = #11/2/2014#


Set rst = qdf.OpenRecordset()

End Sub

Nothing seems to happen. Should the query open and show on the screen? I have tried a few code variations of the above based on my internet searches but nothing works.

Upvotes: 0

Views: 850

Answers (3)

Gustav
Gustav

Reputation: 55806

The easy method is to remove the parameters from the query and apply a filter to the report:

Dim StartDate As Date
Dim EndDate As Date

StartDate = #10/2/2014#
EndDate = #11/2/2014#

DoCmd.OpenReport "YourReport", , , "[YourDateField] Between #" & Format(StartDate, "yyyy\/mm\/dd") & "# And #" & Format(EndDate, "yyyy\/mm\/dd") & "#"

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

I don't think it can be done the way simple way you want.

This seems a shame because you've followed what could be considered 'best practise' by using a stored procedure with parameters parameters, rather than constructing dynamic SQL.

The truth is, even if it is good practise generally, you are guilty of going against the natural flow of how the Access Team want you use its software! Looks like you are compelled to dynamically construct a WHERE clause to squirt into the provided method :(

Upvotes: 0

MJH
MJH

Reputation: 2307

Nothing is happening because you're not doing anything with qdf and rst after you set them. I'm not entirely sure what you're trying to accomplish, but:


1. If you want to open a Report whose RecordSource is the results of the Query (with parameters dynamically set), try placing the following in the Report's code module:

Private Sub Report_Open(Cancel As Integer)
    Dim db As DAO.Database: Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim sql As String

    Set qdf = db.QueryDefs("MixByRangeA")

    sql = qdf.sql
    sql = Replace(sql, "[StartDate]", "#10/2/2014#")
    sql = Replace(sql, "[EndDate]", "#11/2/2014#")
    Me.RecordSource = sql

    On Error Resume Next
    qdf.Close: Set qdf = Nothing
    db.Close: Set db = Nothing
End Sub

And in the Form, use this to open the Report:

Private Sub AutoReport1_Click()
    DoCmd.OpenReport "Report1", acViewReport
End Sub


2. If instead you just want to open the Query (with parameters dynamically set), try the following:

Public Sub AutoReport1_Click()
    Dim db As DAO.Database: Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim sql As String

    Set qdf = db.QueryDefs("MixByRangeA")

    sql = qdf.sql
    sql = Replace(sql, "[StartDate]", "#10/2/2014#")
    sql = Replace(sql, "[EndDate]", "#11/2/2014#")
    qdf.sql = sql
    DoCmd.OpenQuery (qdf.Name)

    On Error Resume Next
    qdf.Close: Set qdf = Nothing
    db.Close: Set db = Nothing
End Sub

Upvotes: 1

Related Questions