Solved

Filter form

Posted on 2011-03-05
7
259 Views
Last Modified: 2012-05-11
Experts,
I have a filter form for a report.  
The filter form has 4 unbound fields.
What is the trick to making the report open if some of the unbound fields are Null?  
Are you suppose to add <>False or True under IsNull([forms]![frmLCIssuedQryType]![cboCurrency])
I continually play around with the control source of the report by adding or taking away criteria based on  whether or not an unbound filed is null or not null and I just get no where with it for filter forms that have many unbound fields.   I am missing something and I am spending too much time on this.  I need help.  Give me the expert insider trick to doing this that can be applied to each filter form?  
Please see attachment
Thanks
 sql of report
0
Comment
Question by:pdvsa
  • 5
7 Comments
 

Author Comment

by:pdvsa
ID: 35044679
For example, in the above screen print, if the filter form had a null for txtLCType then I want it to show all LCTypes but based on whether or not the other unbound fields were null or not null.   Hope you follow...ask if not...
0
 

Author Comment

by:pdvsa
ID: 35044957
I am thinking that for each unbound field on the filter, you need two columns in the qry design:  one that is the actual field dragged down and the other that has an IIF statement that handles the null on the filter form (if the unbound field was null).  There must be a process for this to handle each unbound field.  I am lost...need help.
0
 

Author Comment

by:pdvsa
ID: 35044962
I woudl post an SQL but I dont think that wil help too much.  The process is what I am looking for.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 35045970
So you've built a form to allow the user to select filter criteria for your report?

If so, you might consider basing your report on the full Query (WITHOUT the criteria), and then using the WHERE argument of the OpenReport method to provide the filtering. This gives you quite a bit more lattitude in regard to filtering, since you can build that WHERE argument to show exactly what you need.
0
 

Author Comment

by:pdvsa
ID: 35046124
Lsm:  i think you mentioned this before. I have always had the criteria inside the report and not within the form.  I know what you mean but i dont really know how to do it.  Can you provide a general exaple that maybe i can adjust to fit my needs?

Thank you  
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 35070337
Just some notes and advice here, ...not really a solution, ...so I am not seeking any points here.

First, if it were me, I would not allow Nulls in the criteria  textboxes.
The user must provide something.
Either that, or the textbox should default to "All" or "None"

Many people think that it's cool if "Null" meant "All". (Show all in nothing is entered)
To me, this has always seemed odd...
It's like if you did not select anything form a menu at a restaurant, the waiter would assume you wanted everything...
;-)

Again, IMHO Null criterion causes to many issues.
(IsNull, NZ, Or...etc, ...as you have in your query...)

Yes, ...It is more difficult to add default "None" or "All" to your interface, but to me, it is worth it.

But as far as your issue is concerned here, I would go with LSM's suggestion.
<Lsm:  i think you mentioned this before.>
He does, and I'll tell you a funny story.

When I first started out as an Expert here, I did what you are doing... Load the criteria into the report via a parameter query.
I always wondered why doing the filter with the OpenReport method was what the other Experts here were always recommending.
(at the time I was not good at VBA coding)
:-O

There are many reasons why it is better to do the filter via OpenReport, but for me it was the fact that you can still run the report by itself, without the annoying Parameter prompts.
Using OpenReport to filter also leaves you free to use the plain query as the source for a form as well.

;-)

I'll let LSM guide you through the implementation.

;-)

JeffCoachman




0
 

Author Comment

by:pdvsa
ID: 35086652
thanks for the advice and that bit of information about why it is recommend to have the criteria in OpenReport.  I think I understand this.  YOu have the Docmd.open report "  " then have the where stuff after in some section after a comma.  Intellisense will tell me this.  

thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 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

22 Experts available now in Live!

Get 1:1 Help Now