Solved

Form Filter and unbound text box

Posted on 2015-01-26
2
409 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
[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
2 Comments
 
LVL 38

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

628 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