Passing report selection criteria to query parameter 1

[ShabanaHafiz]

I am using Microsoft Access 2003.

I created a report using Report Wizard. I selected the query VHRunningMaint (Vehicle Running and Maintenance) where the object’s data comes from. VHRunningMaint query has two parameters:

PARAMETERS StartDate DateTime, EndDate DateTime;

When I click the Preview Option for the Report rptVehicle, I have to enter StartDate and EndDate in Enter Parameter Value dialog box. Then report is opened and it filters the record based on StartDate and EndDate supplied.

I need to create a selection criteria form. In this form, I need to ask StartDate and EndDate from user. Upon clicking Preview Report button, I need to supply values of cboFromDate and cboToDate to the query VHRunningMaint.

In the selection criteria form, Preview Report button has the following code:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

    Dim stDocName, strFilter As String

    strFilter = "[StartDate] = #" & Me.cboFromDate & "# and " & _
    "[EndDate] = #" & Me.cboToDate & "#"

    stDocName = "rptVehicle"
    DoCmd.OpenReport stDocName, acPreview, , strFilter

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

When I click Command0 button, still it prompts for StartDate and EndDate in the Enter Parameter Value dialog box. Query VHRunningMaint is not getting StartDate and EndDate values of cboFromDate and cboToDate

In the help, it says that FilterName (Argument 3) is a string expression that's the valid name of a query and WhereCondition (Argument 4) is a string expression that's a valid SQL WHERE clause. So I am not sure, where to put the values for Query Parameters.

 

[ShabanaHafiz]

I removed PARAMETERS statement from query:

PARAMETERS StartDate DateTime, EndDate DateTime;

In the Design view of query, I entered the following in Date criteria:

Between [Forms]![rpt1Select]![cboFromDate] And [Forms]![rpt1Select]![cboToDate]

and it worked.

 

[dhookom]

You could possibly remove the date criteria stuff from the query and use code like:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

    Dim stDocName, strFilter As String

    strFilter = "[DateField] Between #" & Me.cboFromDate _
        & "# and #" & Me.cboToDate & "#"

    stDocName = "rptVehicle"
    DoCmd.OpenReport stDocName, acPreview, , strFilter

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]