Solved

Form Filter and unbound text box

Posted on 2015-01-26
2
400 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 36

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

830 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