Solved

Filter form if text string is anywhere in table

Posted on 2011-03-19
5
233 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
ID: 35171865
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
ID: 35172365
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
ID: 35172540
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
ID: 35172600
Thanks for the assist.

Everything is working great.
0
 
LVL 1

Author Closing Comment

by:Scamquist
ID: 35172603
I knew about the OR statement.  Just didn't occurr to me to use it.
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

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

911 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

22 Experts available now in Live!

Get 1:1 Help Now