?
Solved

Search all fields in Access 2007

Posted on 2011-05-02
18
Medium Priority
?
247 Views
Last Modified: 2012-08-13
My customer wants to be able to type in a keyword with wild card ability and search every field within the current record set. Then change the current record set to only those records containing the keyword in any field. (simular to a filter, but useing all fields)

I don't see an easy way to do this without writing tons of code. Can anyone help?
Thanks!  
0
Comment
Question by:WillbrosIT
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35504946
<search every field within the current record set>
you can do this by building a string as filter, something like this

dim str as string,j as integer, rs as dao.recordset
set rs= me.recordsetclone

for j=0 to rs.fields.count-1
    if str & ""="" then
        str = rs(j).name like '*" & me.txtsearch & "*'
        else
        str=str & " And rs(j).name like '*" & me.txtsearch & "*'
    end if
next

me.filter=str
me.filteron=true



0
 
LVL 12

Expert Comment

by:rgn2121
ID: 35504977
You could use something similar to the code below to move through the fields in a recordset.  Then if you find a match you could add the PK for that entry to a string variable WHERE clause that you would build to use to return on the filtered data.

So each time something is found that matches you would have something like
whereclause = whereclause & " AND MyField = " & rs!myPK

Once you went through the fields in the recordset, you should have a where clause built so you can pull a new recordset with only the needed info...

That is my first thought...
If rs.recordCount > 0 Then
    rs.MoveFirst
        While Not rs.EOF
        
            For i = 0 To rs.Fields.Count - 1
            
                If IsNull(rs.Fields(i)) Then
                    MsgBox "A Null value was found...Please check your entries.", vbCritical, "Null(s) Found"
                    Exit Function
                End If
            
            Next i
        
        rs.MoveNext
       Wend
End If

Open in new window

0
 

Author Comment

by:WillbrosIT
ID: 35505548
Using this code with me.searchtext replaced with the user input string, I keep getting an error, saying invalid use of '.' or "()" or "!" in the filter string. Any idea why?
dim str as string,j as integer, rs as dao.recordset
set rs= me.recordsetclone

for j=0 to rs.fields.count-1
    if str & ""="" then
        str = rs(j).name like '*" & me.txtsearch & "*'
        else
        str=str & " And rs(j).name like '*" & me.txtsearch & "*'
    end if
next

me.filter=str
me.filteron=true
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 35505616
You probably need to insert an IF statement to make sure the field is actually a text field before appending to the filter.  Something like:

for j=0 to rs.fields.count-1
    If fld.Type = dbText Or fld.Type = dbMemo Then
        if str & ""="" then
            str = rs(j).name like '*" & me.txtsearch & "*'
        else
            str=str & " And rs(j).name like '*" & me.txtsearch & "*'
        end if
    end if
next

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35505634
Capricorn1,

I've never seen the syntax you use in your If statement before (except one other post of yours this morning).  what does this accomplish?

if str & ""=""

or

[fieldname] & ""<>""
0
 

Author Comment

by:WillbrosIT
ID: 35505885
This did not help.  >    If fld.Type = dbText Or fld.Type = dbMemo Then
Something about the format of str it just does not like.
I change the loop to be from j=0 to 2 and I still get the error. stumped!
0
 

Author Comment

by:WillbrosIT
ID: 35505916
my code now:

Private Sub Command212_Click()
On Error GoTo Err_Command212_Click
Dim strin As String, str As String, j As Integer, rs As dao.Recordset
Set rs = Me.RecordsetClone
strin = InputBox("Enter your  search string", "Search by KeyWord")

For j = 0 To 4 'rs.Fields.Count - 1
    If rs(j).Type = dbText Or rs(j).Type = dbMemo Then
   
        If str & "" = "" Then
            str = " rs(j).Name like '*" & strin & "*'"
            Else
            str = str & " or rs(j).Name like '*" & strin & "*'"
        End If
    End If
Next
MsgBox (str)
Me.Filter = str
Me.FilterOn = True

Exit_Command212_Click:
    Exit Sub

Err_Command212_Click:
    MsgBox Err.Description
    Resume Exit_Command212_Click
   
End Sub
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 35505952
sorry, I just copied the syntax from one of my samples, so the reference to fld would not work.  Additionally, you need to actually get the field name into the SQL string, and must close the string with a ".  Try the following:

for j=0 to rs.fields.count-1
    If rs.fields(j).Type = dbText Or rs.fields(j).Type = dbMemo Then
        if str & ""="" then
            str = "[" & rs(j).name & "] like '*" & me.txtsearch & "*'"
        else
            str=str & " And [" & rs(j).name & "] like '*" & me.txtsearch & "*'"
        end if
    end if
next
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35506003
WillbrosIT

change this part of your code

MsgBox (str)

with

debug.print str

this print the built string "str" in the immediate window,
copy the printed string and post back here
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35506072
capricorn1,

you were missing a couple of quotes in your original syntax.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35506146
this is great!!!!
0
 

Author Comment

by:WillbrosIT
ID: 35506156
This works now, but I have about 70 fields, so I get an error "too long".
If I change to somthing like j=0 to 12 , it work fine.
Must be string limit I guess.....
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35506211
Chances are that if you have 70 text fields in your recordset, you need to relook your data structure.

However, with that in mind, you could identify specific fields that you want to include in the query, and just do something like:

str = "[Field1] like '*" & me.txtsearch & "*' OR " _
     & "[Field5] like '*" & me.txtsearch & "*' OR " _
     & "[Field17] like '*" & me.txtsearch & "*' OR " _
     & "[Field23] like '*" & me.txtsearch & "*'"



0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35506232
Capricorn1,

Did you see my question in this thread
0
 

Author Comment

by:WillbrosIT
ID: 35506718
I have no chocie of the data structure or number of fields, however you are right, many of these fields are check boxes, etc. that I can skip. Maybe I can get it to fit...
Thanks for all your help!
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35506759
If the fields are checkboxes, they shouldn't pass the "Type" portion of the If statement, and should not affect the length of your filter string.  

Right after the For / Next loop ends, insert a line like:

debug.print str

This will print out the resulting SQL string in your immediate window.  Cut that text and paste it here so we can see what you are dealing with.

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35506772
You should probably request attention to open this question back up, or you won't get anyone else to chime in.  I'll be glad to stick with you, but now that the question is answered, the only other person likely to chime in is Capricorn1.
0
 

Author Comment

by:WillbrosIT
ID: 35507169
I got it all working now, thanks guys!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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