Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Form Filter and unbound text box

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
pdvsa
Asked:
pdvsa
1 Solution
 
PatHartmanCommented:
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
 
pdvsaProject financeAuthor Commented:
Nice!  that worked.  

thank you very much for that code.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now