Solved

Filter form

Posted on 2011-03-05
7
271 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
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…
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 …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

920 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

17 Experts available now in Live!

Get 1:1 Help Now