?
Solved

Form Filter and unbound text box

Posted on 2015-01-26
2
Medium Priority
?
417 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 39

Accepted Solution

by:
PatHartman earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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