Solved

Open Report , Where condition

Posted on 2013-05-26
7
1,364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

691 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