Solved

Filter form

Posted on 2011-03-05
7
305 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 85

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 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