OpenReport WhereCondition not working 1

[damo317]

Hi,

I'm using access2000 to create a dynamic report based on inputs from a form. Both the query and report do not reference the form in any way. I want to be able to select only certain rows from the query, so I thought I would use the WhereCondition field in DoCmd.OpenReport, but this seems to have no effect on the output whatsoever.

stDocName = "Report1"

cwh = SeenWhere.Value
If cwh = 1 Then
whereStr = &quot; initial_contact_where <> 'Campus'&quot;
ElseIf cwh = 2 Then
whereStr = &quot; initial_contact_where = 'Campus'&quot;
ElseIf cwh = 3 Then
whereStr = &quot; 0 = 1&quot;
End If

DoCmd.OpenReport stDocName, acPreview, stDocName, whereStr

initial_contact_where is a field in the query. I put the &quot; 0 = 1&quot; in there to test if the where condition was working at all, but even with this it returns all rows from the report.

I have tried setting RecordSource for the report to be the query name, and the actual SQL for the query but both return the exact same result.

I assume (from reading the help) that the WhereCondition just basically adds &quot;WHERE &quot; & whereStr to the end of the SQL query for the reports RecordSource, but it doesn't seem to do a thing. Is this correct? Am I doing something wrong?


damo317

 

[dhookom]

Why do you have stDocName twice. Try remove the second stDocName.
DoCmd.OpenReport stDocName, acPreview, , whereStr

The &quot;Where&quot; clause actually sets and then applies the Filter property of the report.

 

[damo317]

I had put the 2nd stDocName in while trying to get this thing to work. It makes no difference if I remove it.

I just tried putting the clauses in to the Filter property and it works as it should. I then tried updating the .Filter property from my code with whereStr and it also works as it should. So I guess I can do what I want to achieve, but I don't understand why the WhereCondition of OpenReport isn't doing what its supposed to.

 

[dhookom]

Have you placed a break point in your code and stepped through it? You might want to try use Select Case rather than multiple IFs.

stDocName = &quot;Report1&quot;

cwh = SeenWhere.Value
Select Case cwh
Case 1
whereStr = &quot; initial_contact_where <> 'Campus'&quot;
Case 2
whereStr = &quot; initial_contact_where = 'Campus'&quot;
Case 3
whereStr = &quot; 0 = 1&quot;
End Select

DoCmd.OpenReport stDocName, acPreview, , whereStr

 

[damo317]

I don't think it is the If's thats that issue, because even doing:
DoCmd.OpenReport stDocName, acPreview, , &quot; 0 = 1&quot;
returns all rows from the query/report

 

[dhookom]

I would place a text box in the report header with a control source of:
=[Filter]
This should display the where clause (if any) that is applied to the report.

 

[damo317]

Ok I tried that but nothing showed up.
If I manually set the Filter property in my code (ie Reports!Report1.Filter = whereStr) it displays, but using wherecondition shows nothing.
I also added - MsgBox Reports!Report1.Filter after DoCmd.OpenReport and that displays an empty box if I'm using wherecondition, and the correct filter if i update it myself.

So I guess this means that for some reason the where clause isn't being applied by using WhereCondition.

 

[schwarem]

I am having this same problem. Did you find a solution?

 

[jiqjaq]

The Where parameter is Before the RecordSource is applied. The Filter property is After.

The Filter and Where condition are NOT the same thing. With a Filter, the Recordsource is used as is, i.e., the report query is run as given. All the records are then returned but only those that match the Filter are passed onto the report for further processing.

With a Where, it's as if a Where clause was tacked onto the end of the RecordSource. The RecordSource with this new Where clause appended to it is then used as the report's actual query.

If the RecordSource already has a Where clause or if it ends in some other clause that mornally follows the Where (Group By, Having, etc.), the OpenReport Where parameter won't work correctly. You can't tack on a Where clause at the end if it doesn't normally belong there.

 

[jmstarbuck]

I can't seem to get either the where or filter to work using the DoCmd.OpenReport method. If I try to set the filter before the DoCmd.OpenReport, I get an error that the report is either misspelled or not open. Well, it is not open. Is there a way to open the report without the user seeing it, so that I can then update the properties and then print the report?

 

[dhookom]

What is your code? Can you show us how you are attempting to use the where clause in the DoCmd.OpenReport method?

Duane
MS Access MVP