multiple field key word search..... can anyone put me out of my misery

[idunkown1]

Hi Guys and girlies

Hope you can help???....... can any body help....?

I am currently building an indexing system which has lots of fields and I need it to do multiple searches. The form has tick boxes (yes/no),date/time boxes, memo boxes and text boxes which people will use simultanously to search for items. Please take into account that different people will search for different things so I need the search mechanism or code to be flexible. Producing results depending on which boxes they have ticked, information they have added to a memo or text box. Any ideas on how I can creat this search mechanism?? If its best to use a querry what do I need to write in the querry and each field to perform the search. If I have to do a macro what programming code do I need to add? and where? so that people can search for different items by using all these types of fields simultanously....

Any ideas?

A desperate database builder :-(

PS u guys heard about microsofts mr clippy ??? he's been made redundant can anybody offer him some part time work? he! he!

mon

 

[SimonDavis]

I made a complex query on a futures trading system which builds a text query from the selected criteria, then places the finished text into a variable, and filters on that.

It's a big pile of code, and I couldn't say it's the best way, but have a pick through this:

The form is a simple worksheet with all the transactions listed. Some of the fields have a combo box at the bottom, where the filter criteria can be chosen. The difficulty (as you know) is building the query with multiple criteria. This is what I did, and it works!

This is the code for one of the combo boxes, but it checks the others too.

Private Sub brokcombo_AfterUpdate()
DoCmd.ShowAllRecords
Dim first As String, catquery As String
Dim mth_and As String, mth2_and _
As String, cln_and As String, ctr_and As String
first = ""
catquery = ""
mth_and = ""
mth2_and = ""
cln_and = ""
ctr_and = ""
If IsNull(brokcombo.Value) = True Then GoTo evm1
first = "newbroker = '" & brokcombo.Value & "'"
GoTo addit
evm1:
If IsNull(monthcombo.Value) = True Then GoTo evm2
first = "month = '" & monthcombo.Value & "'"
GoTo addit
evm2:
If IsNull(month2combo.Value) = True Then GoTo evcl
first = "month2 = '" & month2combo.Value & "'"
GoTo addit
evcl:
If IsNull(clientcombo.Value) = True Then GoTo evct
first = "newclient = '" & clientcombo.Value & "'"
GoTo addit
evct:
first = "newcontr = '" & contrcombo.Value & "'"
GoTo addit

addit:
Call wkg
If monthcombo.Value <> &quot;&quot; Then mth_and = &quot;and month = '&quot; & monthcombo.Value & &quot;'&quot;
If month2combo.Value <> &quot;&quot; Then mth2_and = &quot;and month2 = '&quot; & month2combo.Value & &quot;'&quot;
If clientcombo.Value <> &quot;&quot; Then cln_and = &quot;and newclient = '&quot; & clientcombo.Value & &quot;'&quot;
If contrcombo.Value <> &quot;&quot; Then ctr_and = &quot;and newcontr = '&quot; & contrcombo.Value & &quot;'&quot;
catquery = first + mth_and + mth2_and + cln_and + ctr_and
DoCmd.ApplyFilter , catquery
End Sub

 

[GSMike]

Hi Idunkown1,
Interesting that you should be facing this. I faced exactly the same thing a couple of months ago.
DISCLAIMER: I never resolved the issue because the project was shelved, but I spent a lot of time brainstorming and I think I have the concept down; you'll have to work out the details. Sorry I can't be more definitive, but I'll give you what I have!!
I believe that you will have to create your query through code with dynamic where statements being appended to your SQL string, based on what &quot;tick boxes&quot; your users select. So, try this:

Dim dbs as database, qdf as querydef, sql_1 as string,  dynwhere_1 as string, dynwhere_2 as string, dynwhere_3 as string, dynwhere_4 as string, etcetc, x as string
set dbs=currentDB
set qdf=dbs.createquerydef("mynewquery")
if forms![your form]![opgr_yesno]<>0 or not(isnull(forms![your form]![datepicker])) or not(isnull(forms![your form]![yourtextbox])) or not(isnull(forms![your form]![yourmemobox])) then
     if forms![your form]![opgr_yesno]=1 then 
          dynwhere_1=&quot;yesno=1&quot;
          x=a
     else
          dynwhere_1=&quot;yesno=2&quot;
          x=a
     end if
     if not(isnull(forms![your form]![datepicker])) then
          dynwhere_2=&quot;datefield=forms![your form]!
          [datepicker]
          x=x & &quot;b&quot;
     end if
     if not(isnull(forms![your form]![yourtextbox])) then
          dynwhere_3=&quot;textfield=forms![your form]!
          [yourtextbox]
          x=x & &quot;c&quot;
     end if
     <same for memobox>
'This part you'll have work out, how to concatenate the 
'dynamic where's and put &quot;and&quot; between them, etc, but that 
'is the purpose of the x=x & &quot;?&quot;
     select case x
          case &quot;a&quot;
             sql_1=&quot;select * from yourtable where &quot; & 
             dynwhere_1 & &quot;;&quot;
          case &quot;ab&quot;
             sql_1=&quot;select * from yourtable where &quot; & 
             dynwhere_1 & &quot; and &quot; & dynwhere_2 & &quot;;&quot;
          case &quot;abc&quot;
             sql_1=&quot;select * from yourtable where &quot; & 
             dynwhere_1 & &quot; and &quot; & dynwhere_2 & &quot; and &quot; & 
             dynwhere_3 & &quot;;&quot;
           <keep going with this until you have used every 
           possible combination of potential search 
           criteria***>
     end select
else
     sql_1=&quot;select * from yourtable;&quot;
end if
qdf.sql=sql_1
docmd.applyfilter ,&quot;mynewquery&quot;
end sub

[/color blue]

***If you have like 20 potential search criteria, you'll have to look for a more sophisticated solution to your dilemma (using a similar concept perhaps...aha, in the if statements, you could create your sql as you go along; so create a predynwhere string of &quot;where&quot; and then as you proceed through the if tests, if x=1 (use an integer instead of a string for x) then it would be sql_1=predynwhere & &quot;yesno=1(2)&quot;;the next criteria that was met would be sql_1=sql_1 & &quot;and datefield=datepicker&quot;, etc. then at the end do sql_1=sql_1 & &quot;;&quot. If you only have four or five, this solution is workable, I believe.

Hope this helps, as opposed to hurting!!!
-Mike

 

[PaulF]

another method would be to build a Query with the QBE and use:
= Forms!FormName!ControlName
or
Like Forms!FormName!ControlName & &quot;*&quot;
or
Like &quot;*&quot; & Forms!FormName!ControlName
or
Like &quot;*&quot; & Forms!FormName!ControlName & &quot;*&quot;

for each of the Criteria Rows in your Query. You can use which one best suits the Field. Then depending on whether all conditions must be met (AND)or if any of the conditions can exist (OR) will determine whether you use different Criteria Rows in the QBE or place them all on the same Criteria Row

PaulF