×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

OpenReport WhereCondition not working

OpenReport WhereCondition not working

OpenReport WhereCondition not working

(OP)
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 = " initial_contact_where <> 'Campus'"
    ElseIf cwh = 2 Then
        whereStr = " initial_contact_where = 'Campus'"
    ElseIf cwh = 3 Then
        whereStr = " 0 = 1"
    End If

DoCmd.OpenReport stDocName, acPreview, stDocName, whereStr

initial_contact_where is a field in the query. I put the " 0 = 1" 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 "WHERE " & 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

RE: OpenReport WhereCondition not working

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

The "Where" clause actually sets and then applies the Filter property of the report.

RE: OpenReport WhereCondition not working

(OP)
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.

RE: OpenReport WhereCondition not working

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 = "Report1"

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

DoCmd.OpenReport stDocName, acPreview, , whereStr
 

RE: OpenReport WhereCondition not working

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

RE: OpenReport WhereCondition not working

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.

RE: OpenReport WhereCondition not working

(OP)
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.


RE: OpenReport WhereCondition not working

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

RE: OpenReport WhereCondition not working

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.

RE: OpenReport WhereCondition not working

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?

RE: OpenReport WhereCondition not working

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close