Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Custom filters based on textboxes

Posted on 2013-07-01
4
Medium Priority
?
524 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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

972 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