Solved

Open Report , Where condition

Posted on 2013-05-26
7
1,194 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

25 Experts available now in Live!

Get 1:1 Help Now