Solved

Custom filters based on textboxes

Posted on 2013-07-01
4
520 Views
Last Modified: 2013-07-01
Dear Experts

I need to develop textboxes working as filters above datasheet form. What I want is to filter multiple columns at the same time using wildcards.
I did it through changing recordsource with query which picks up values from textboxes. The only problem is that it returns blank if table contains null cells.
Any other way of obtaining such custom filters?

this is one of my vba procedures. It uses query filter which picks up text from textboxes
Private Sub Filter_Table()
'
    If IsNull(Me.ParentFilter) Then Me.ParentFilter = "*"
    If IsNull(Me.FlocFilter) Then Me.FlocFilter = "*"
    If IsNull(Me.FlocDescFilter) Then Me.FlocDescFilter = "*"
    If IsNull(Me.DrawingFilter) Then Me.DrawingFilter = "*"
    If IsNull(Me.StatusFilter) Then Me.StatusFilter = "*"
    If IsNull(Me.GridFilter) Then Me.GridFilter = "*"
    If IsNull(Me.ObjectTypeFilter) Then Me.ObjectTypeFilter = "*"
    Me.TreeLevels_subform.Form.RecordSource = "Filter"
    Me.Requery

End Sub

Open in new window

0
Comment
Question by:yahooooo
[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
  • 3
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39291342
Your example is incomplete, but your recordsource query probably has a WHERE clause that contains something similar to the following:


WHERE Parent = "something" AND Floc = "Something Else" AND ....


For your query to work with wildcards as you are trying to do, you would have to change the "=" signs in your query to the LIKE keyword:

WHERE Parent LIKE "something" AND Floc LIKE "Something Else" AND ....


(We'd need more information about your actual query syntax and how your recordsource gets set up in order to give you more precise answers.)
0
 
LVL 3

Author Comment

by:yahooooo
ID: 39291365
nope
its all like like ;)
SELECT TreeLevels.[Superior Function Location], TreeLevels.[Functional location], TreeLevels.[Function Location Description], TreeLevels.[Drawing Ref], TreeLevels.Grid, TreeLevels.[Object type], TreeLevels.Status
FROM TreeLevels
WHERE (((TreeLevels.[Superior Function Location]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![ParentFilter] & "*") AND ((TreeLevels.[Functional location]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![FlocFilter] & "*") AND ((TreeLevels.[Function Location Description]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![FlocDescFilter] & "*") AND ((TreeLevels.[Drawing Ref]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![DrawingFilter] & "*") AND ((TreeLevels.Grid) Like "*" & [Forms]![FirstStage]![FormTagValidation]![GridFilter] & "*") AND ((TreeLevels.[Object type]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![ObjectTypeFilter] & "*") AND ((TreeLevels.Status) Like "*" & [Forms]![FirstStage]![FormTagValidation]![StatusFilter] & "*"));

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39291416
Try running your query without the code that sets null textboxes to *.  (Just leave the textboxes null).
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39291478
Oh!  I think I'm mis-reading your question... are you trying to pick up nulls/blanks in your table data (versus form data)?

Use this query:


SELECT TreeLevels.[Superior Function Location], TreeLevels.[Functional location], TreeLevels.[Function Location Description], TreeLevels.[Drawing Ref], TreeLevels.Grid, TreeLevels.[Object type], TreeLevels.Status
FROM TreeLevels
WHERE ((("" & TreeLevels.[Superior Function Location])   Like "*" & [Forms]![FirstStage]![FormTagValidation]![ParentFilter] & "*") AND (("" & TreeLevels.[Functional location]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![FlocFilter] & "*") AND (("" & TreeLevels.[Function Location Description]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![FlocDescFilter] & "*") AND (("" & TreeLevels.[Drawing Ref]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![DrawingFilter] & "*") AND (("" & TreeLevels.Grid) Like "*" & [Forms]![FirstStage]![FormTagValidation]![GridFilter] & "*") AND (("" & TreeLevels.[Object type]) Like "*" & [Forms]![FirstStage]![FormTagValidation]![ObjectTypeFilter] & "*") AND (("" & TreeLevels.Status) Like "*" & [Forms]![FirstStage]![FormTagValidation]![StatusFilter] & "*"));

Open in new window


(I just added "" & before all of your table's field names, so that the comparison is on an empty string instead of a null (which wont work in this type of comparison).
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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 custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses
Course of the Month6 days, 11 hours left to enroll

623 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