Solved

Form Filter and unbound text box

Posted on 2015-01-26
2
391 Views
Last Modified: 2015-01-26
Experts, I have a filter form for a report.  

On this form I want to filter for department for the date range on the filter form.
I put an unbound text box on the filte rform (txtDept)
However, if the unbound text box (txtDept) is Null then I want to return ALL the data for the date range.

I think the below will need to be used on [Area of Responsibility]
Like "*" & [forms]![frmFilter]![txtDept] & "*"
but adding the part about if the unbound text box is null is the difficult part for me.  I think there are a few ways to do it.  

here is the SQL:
SELECT Import_Excel.*, Import_Excel.[Area of Responsibility], Import_Excel.[From Someone Else]
FROM Import_Excel
WHERE (((Import_Excel.[From Someone Else]) Is Null) AND ((Import_Excel.[Due Date per docs])>=[forms]![frmFilter_ShortByMonth]![txtDateFrom] And (Import_Excel.[Due Date per docs])<[forms]![frmFilter_ShortByMonth]![txtDateTo]+1)) OR (((Import_Excel.[From Someone Else]) Is Null) AND ((Import_Excel.[Due Date per docs]) Is Null));

thank you
0
Comment
Question by:pdvsa
2 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40571581
Access goes hog-wild with parentheses and so it is sometimes difficult to sort out what is meaningful.

WHERE
(Import_Excel.[From Someone Else] Is Null
AND Import_Excel.[Due Date per docs] >= [forms]![frmFilter_ShortByMonth]![txtDateFrom] And Import_Excel.[Due Date per docs] < [forms]![frmFilter_ShortByMonth]![txtDateTo]+1)
OR
(Import_Excel.[From Someone Else] Is Null
AND Import_Excel.[Due Date per docs] Is Null);

I put back the parentheses where I think they logically go.  If you don't agree, please change them since I don't think the expressions make logical sense.  I would write the above expression as

WHERE
Import_Excel.[From Someone Else] Is Null

AND ((Import_Excel.[Due Date per docs] >= [forms]![frmFilter_ShortByMonth]![txtDateFrom] And Import_Excel.[Due Date per docs] < [forms]![frmFilter_ShortByMonth]![txtDateTo]+1)
OR
Import_Excel.[Due Date per docs] Is Null)

AND (Import_Excel.[Area of Responsibility] = [forms]![frmFilter]![txtDept] OR [forms]![frmFilter]![txtDept]  Is Null)


NEVER use LIKE unless you really are searching for partial strings.  LIKE can prevent the query engine from using an available index and instead forces a full table scan.  For small amounts of data, you won't notice slowness but you will if your tables are large.  The expression I added at the end will select records where the table field matches the form field or the form field is null.
0
 

Author Closing Comment

by:pdvsa
ID: 40571622
Nice!  that worked.  

thank you very much for that code.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

13 Experts available now in Live!

Get 1:1 Help Now