Improve company productivity with a Business Account.Sign Up

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

Custom filters based on textboxes

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
yahooooo
Asked:
yahooooo
  • 3
1 Solution
 
mbizupCommented:
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
 
yahoooooAuthor Commented:
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
 
mbizupCommented:
Try running your query without the code that sets null textboxes to *.  (Just leave the textboxes null).
0
 
mbizupCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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