Solved

Custom filters based on textboxes

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now