Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filter form

Posted on 2011-03-05
7
Medium Priority
?
316 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
[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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 1000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

597 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