Solved

Filter form

Posted on 2011-03-05
7
289 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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