Solved

Custom filters based on textboxes

Posted on 2013-07-01
4
518 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ListData refresh onClick 5 40
vb.net dbnull syntax 1 47
Access Query function 4 43
how to get hundreds part from the number 1 23
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

742 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