Solved

Open Report , Where condition

Posted on 2013-05-26
7
1,153 Views
Last Modified: 2013-05-27
Experts, how do I open the report with the where condition below?  There is an error on the bold part.   thank you

Private Sub Command74_Click()

    Minimize
    DoCmd.OpenReport "rptQry_FX_Expected", acViewPreview,,[Status] Not Like "*Dead*" And Not Like "*lost*" And Not Like "*Award*" And Not Like "*Won*"

End Sub
0
Comment
Question by:pdvsa
  • 4
  • 3
7 Comments
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 385 total points
ID: 39197993
you can put it in the query that the Report is based on, if the Report is based on a Table, then make a query for it, and put this condition as a criteria under the [Status] field.

the option it to insert it in the Report's Filter.

jaffer
0
 

Author Comment

by:pdvsa
ID: 39198004
Hi jaffer and thanks for the response. actually I do have it this way (where condition in the query) and I want to open the report a more dynamic way by having the where condition in the button instead of the query.   I have a few different buttons on the form and each has its own where condition criteria.  

What do you think now?  thank you
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39198018
so you want to use one query, yet have different buttons on the Form, each with a different condition (Filter ing the query further),
In this case, one way to do this is by using the query as is, and using the Report Filter for the further "Filtering".

Assuming your command button event is as you mentioned above:

Private Sub Command74_Click()

    Minimize
    DoCmd.OpenReport "rptQry_FX_Expected", acViewPreview
    Reports!rptQry_FX_Expected.Filter="[Status] Not Like '*Dead*' And [Status] Not Like '*lost*' And [Status] Not Like '*Award*' And [Status] Not Like '*Won*'"
    Reports!rptQry_FX_Expected.FilterOn=true

End Sub

Open in new window


You just have to check your Where condition syntax, and the query is the best place to fine tune it.

jaffer
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pdvsa
ID: 39198025
ahhhh...I see ....I have never used that before.  Looks nice.   I will test in a couple hours.  I am sure that is what I am looking for though.
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 385 total points
ID: 39198031
Please allow me to show you how I do it :)

Open any Form, in the Design view, look at the Form's properties > Filter,
it should be empty,
Now switch to Layout view, right click on one of the field values, and select "equals to",
now go back to the design view and see what the Form Filter property is :)
add another Filters to it, and see the Filter, .... and so on,
now you can use that in your Report :)

For your case, look for "Does not contain", which might help you build your Filter :)
and you can use the wildcat '*'  (without the quotes) too (assuming you are using access 2007 or greater (that is for the "Does not contain").


jaffer
0
 

Author Closing Comment

by:pdvsa
ID: 39199626
very nice!  thank you for the explanation too.  I wrote that down.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39199631
you are welcome, and thanks for the points and grade :)

jaffer
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now