Reports based on queries

[Merkava]

Hello,

I have a report that is based on a query that needs to receive three parameters.

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
    Dim stDocName As String
    stDocName = "qryAbsenteeismdate"
    DoCmd.OpenReport stDocName, acPreview, , "[AgentID]=" & cmbAgents.Value & " and" & "[StartDate] = #12/1/09#" & " and" & "[EndDate] = #12/31/09#"
 
Exit_Command1_Click:
   Exit Sub
Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
End Sub

The parameters are being ignored. When the code is run, I receive the input boxes requesting the parameters.

Cheers,
Aaron

[RuralGuy]

It should work if you have the Field Names correct. The WhereCondition argument puts a filter on the RecordSource of the Report. If it is asking for those parameters then either it thinks they are spelled wrong or the Report does not start woth a defined RecordSource.

[Merkava]

Hi,

To remove the chance of a spelling mistake I simplified the parameter name in the query. It looks as such:

SELECT tblAgents.fldFirstName, tblAgents.fldLastName, tblAbsenteeism.fldDateReported, tblAbsenteeism.fldDidNotify, tblAbsenteeism.fldComments, tblAbsenteeism.fldSickNoteProvided
FROM (tblTeam RIGHT JOIN (tblAbsenteeism INNER JOIN tblAgents ON tblAbsenteeism.fldAgentID = tblAgents.fldAgentID) ON tblTeam.fldTeamID = tblAgents.fldTeamID) INNER JOIN tblSubCategory ON tblAbsenteeism.fldSubCategoryID = tblSubCategory.fldSubCategoryID
WHERE (((tblAbsenteeism.fldDateReported)>=[S] And (tblAbsenteeism.fldDateReported)<=[E]) AND ((tblAbsenteeism.fldAgentID)=[A]));

Here is the code in the form that calls the report:

 stDocName = "qryAbsenteeismReportByAgent"
        strWhere = "[A]=" & cmbAgents.Value & " and" & "[S] = #" & Date & "# and [E] = #" & Date & "#"
        DoCmd.OpenReport stDocName, acPreview, , strWhere

I used the wizard to create the report. I will try and attach a screen shot of the properties

The parameters are still being ignored.

Cheers,
Aaron

[RuralGuy]

It looks like your spelling simplification guarantees that Access will ask for the parameters.

[RuralGuy]

You do not pass parameters to a report using the WhereCondition argument of the OpenReport command. That argument is simply to put a filter on the RecordSource of the report.

[Rawb]

You do not pass parameters to a report using the WhereCondition argument of the OpenReport command. That argument is simply to put a filter on the RecordSource of the report.

What I think he means here is "Take out the WHERE argument in your Query."

The WhereCondition argument basically builds a "WHERE" SQL statement and tacks it on at the end of the original Query so most of the time, you don't need to have one in the original Query.

After that, you'll need to change your WhereCondition to use the actual field names in your Query (for example [fldAgentID] instead of [AgentID]).

[Merkava]

Hi,

There are two issues here:
1) When I try and run the query with the parameters, Access still requesting that the user inputs the parameters.

2) My report is based on a query that receives the parameters from the form. Is there an example of placing a recordset on the report??

I appreciate the time that has been taken to assist me.

Cheers,
Aaron

[RuralGuy]

I would use a generic query as the RecordSource of the Report and apply the filter or WHERE Clause in the OpenReport command. The report when run by itself should not ask for any data.

[Merkava]

I would use a generic query as the RecordSource of the Report and apply the filter or WHERE Clause in the OpenReport command. The report when run by itself should not ask for any data.

Hi,

This is getting quite confusing for me at this point. I thought that the best way would be to have a report based on a query that would receive the reqired parameters from the controls on the form.

To implement your suggestion:

In the properties I have the query that I wish to use. Are you suggesting that I run the SQL in the recordset at run-time instead of having a "ready made" query that requests parameters ?

For example:

DoCmd.OpenReport stDocName, acPreview, , "Select fldAgentID, fldFName where fldTeamID=" & cmbTeams.value & " and fldDateEntered >=" & #date1# & " and fldDateEntered <=" & #Date2#

Is this what you are referring to?

[RuralGuy]

No. I was suggesting the report have a static RecordSource that has no additional criteria and basically pulls all of the records in the query. Then you can limit the report with the WhereCondition argument as you have been doing.

[Merkava]

Hi RG,

I played with what you said. I removed the parameters from the original query and used that as the record set

In the Filter portion on the docmd I did the following:

 stDocName = "qryAbsenteeismReportByAgent"

 strFilter = "SELECT tblAgents.fldFirstName, tblAgents.fldLastName, tblAbsenteeism.fldDateReported, tblAbsenteeism.fldDidNotify, tblAbsenteeism.fldComments, tblAbsenteeism.fldSickNoteProvided, tblAbsenteeism.fldDateReported, tblAbsenteeism.fldAgentID"
        strFilter = strFilter & " FROM (tblTeam RIGHT JOIN (tblAbsenteeism INNER JOIN tblAgents ON tblAbsenteeism.fldAgentID = tblAgents.fldAgentID) ON tblTeam.fldTeamID = tblAgents.fldTeamID) INNER JOIN tblSubCategory ON tblAbsenteeism.fldSubCategoryID = tblSubCategory.fldSubCategoryID"
        strFilter = strFilter & " WHERE (((tblAbsenteeism.fldDateReported)>=#12/1/2009# And (tblAbsenteeism.fldDateReported)<=#12/31/2009#) AND ((tblAbsenteeism.fldAgentID)=" & cmbAgents.Value & "));"
 
DoCmd.OpenReport stDocName, acPreview, strFilter

This appears to work. Do you see any problems with this solution?

[RuralGuy]

That is not using the WhereCondition as I suggested. I've never used the Filter argument.

[Merkava]

That is not using the WhereCondition as I suggested. I've never used the Filter argument.

Would you be able show me what you mean?

Cheers,
Aaron

[Merkava]

Hi,I have it working the way that you suggested - THANKS!!!Any idea what the difference between the two methods and if there is any advantage to either of them??Cheers,Aaron