Solved

Filter form if text string is anywhere in table

Posted on 2011-03-19
5
223 Views
Last Modified: 2012-05-11
I have a form in datasheet continuous forms view with a few thousand records.

The form is based on a table tblOrderInfo

I am have a nice code to filter based on values in specific columns.
ie. city = Milwaukee or the address contains the word National etc.

I am trying to find a way to filter all rows if a text string is in any column in the table (even fields that are not on the form).

Is this possible?
0
Comment
Question by:Scamquist
  • 3
  • 2
5 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
yes, it is possible, but you have to write codes to search each field of a record.
or you can do a long "OR" criteria for each colum

select * from table
where f1 like  "*" & forms!NameofForm!txtSearch & "*" or  f2 like  "*" & forms!NameofForm!txtSearch & "*" or  f3 like  "*" & forms!NameofForm!txtSearch & "*"
0
 
LVL 1

Author Comment

by:Scamquist
Comment Utility
Yes the OR worked.  I incorporated it into a filter I modified from Allen Browne (allen@allenbrowne.com)

One more question, if I may...

The line is long and I am trying to continue the line with _
but I receive a Compile Error  Expected: end of statement

strWhere = strWhere & "(([OrderNumber] Like ""*" & Me.txtSearchAll & "*"") _
or ([PropertyCity] Like ""*" & Me.txtSearchAll & "*"") _
or ([SecondaryBankerLast] Like ""*" & Me.txtSearchAll & "*"") _
or ([PropertyName] Like ""*" & Me.txtSearchAll & "*"") _
or ([ContactPhone1] Like ""*" & Me.txtSearchAll & "*"") _
or ([PropertyType] Like ""*" & Me.txtSearchAll & "*"") _
or ([ContactPhone1] Like ""*" & Me.txtSearchAll & "*"") _
or ([ContactName1] Like ""*" & Me.txtSearchAll & "*"") _
or ([ContactName2] Like ""*" & Me.txtSearchAll & "*"") _
or ([EngagementComments] Like ""*" & Me.txtSearchAll & "*"") _
or ([NonEngagementComments] Like ""*" & Me.txtSearchAll & "*"") _
or ([AdditionalInformation] Like ""*" & Me.txtSearchAll & "*"")) AND "


Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    '***********************************************************************
    'Look at the search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtSearchAll) Then
        strWhere = strWhere & "(([OrderNumber] Like ""*" & Me.txtSearchAll & "*"") or ([PropertyCity] Like ""*" & Me.txtSearchAll & "*"") or ([SecondaryBankerLast] Like ""*" & Me.txtSearchAll & "*"") or ([PropertyName] Like ""*" & Me.txtSearchAll & "*"") or ([ContactPhone1] Like ""*" & Me.txtSearchAll & "*"") or ([PropertyType] Like ""*" & Me.txtSearchAll & "*"") or ([ContactPhone1] Like ""*" & Me.txtSearchAll & "*"") or ([ContactName1] Like ""*" & Me.txtSearchAll & "*"") or ([ContactName2] Like ""*" & Me.txtSearchAll & "*"") or ([EngagementComments] Like ""*" & Me.txtSearchAll & "*"") or ([NonEngagementComments] Like ""*" & Me.txtSearchAll & "*"") or ([AdditionalInformation] Like ""*" & Me.txtSearchAll & "*"")) AND "
    End If

    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try

strWhere = strWhere & "(([OrderNumber] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([PropertyCity] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([SecondaryBankerLast] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([PropertyName] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([ContactPhone1] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([PropertyType] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([ContactPhone1] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([ContactName1] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([ContactName2] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([EngagementComments] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([NonEngagementComments] Like ""*" & Me.txtSearchAll & "*"")" _
& " or ([AdditionalInformation] Like ""*" & Me.txtSearchAll & "*"")) AND "
0
 
LVL 1

Author Comment

by:Scamquist
Comment Utility
Thanks for the assist.

Everything is working great.
0
 
LVL 1

Author Closing Comment

by:Scamquist
Comment Utility
I knew about the OR statement.  Just didn't occurr to me to use it.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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