Use 'Instr' to search through two fields (1 Viewer)

[Rene vK]

Hello everybody,

In my developing application I am making use of searchboxes to narrow down the amount of records. On a form I have a textbox and a subform with a table connected. In the textbox I can type a character that will be used in an 'Instr' SQL query. I am using the code to query one field. (see code below)

My question is: In what direction do I have to look to make this code usefull to search through two fields. In my case that will be Tag and Function.

Private Sub mnu3_txt_UnitbookSearch_Change()

   Dim SQLstring As String
   SQLstring = "Instr(Tag, " & "'" & Me.mnu3_txt_UnitbookSearch.Text & "'" & ")"
   ReReadDescriptions SQLstring

End Sub

Private Sub ReReadDescriptions(SQLtext As String)

   With Me!frmSUB_Unitbook.Form
     .Filter = SQLtext
     .FilterOn = True
     .Visible = True
     .Requery
   End With

End Sub

All help is appreciated with thanks in advance,
Rene

 

[jdraw]

You're telling us HOW you intend to do some searching. Perhaps if you tell readers WHAT you are trying to accomplish in plain English, there may be some options/alternatives to doing it.

You may find this link useful.

Good luck.

 

[CJ_London]

instr returns a number so to work in a filter, it needs to equal something e.g.

SQLstring = "Instr(Tag, '" & Me.mnu3_txt_UnitbookSearch.Text & "')=0"

will return records where there the search is unsuccessful

SQLstring = "Instr(Tag, '" & Me.mnu3_txt_UnitbookSearch.Text & "')<>0"

will return records where there the search is successful

Notice I have simplified your code slightly

I presume Tag is the name of the field in your form recordset. Although not a reserved word, Tag is a property of form/report controls so you may get some strange errors if your syntax isn't spot on.

Not sure why you want to use instr, the usual way is

SQLstring = "Tag Like '*" & Me.mnu3_txt_UnitbookSearch.Text & "*'"

 

[Rene vK]

Thanks both for the answers!

To be more clear....CJ_London is thinking in the right direction.

- the table in the subform contains 3 fields per record: Tag, Schema, Function.
- In the textbox a character will be typed and this will be compared to values in the 'Tag' field. For instance, P-10 in the textbox will only show the records where this combination is a value in the Tag field.
- The field 'Function' is used for a description like: "Dirty water pump"
- I would like to have the possibility to type "P-10" or "Dirty" in the textbox and the requery will show all records where the first is mentioned in 'Tag' and also the records where the later is mentioned in the field 'Function'
- the code must compare to both fields.

I am using my code in several forms and it works great.
I am using Instr after some carefull looking for a solution in an earlier AutoCAD vba project.

I hope my story is more clear now. (English is not my mothertongue)

 

[gemma-the-husky]

can you concatenate the two search fields into one within your query, and then use that as the object of the instr search.

It may be easier to do it at the query level.

 

[Rene vK]

Dave,

I was also thinking of the concatenate solution. I will get to work with that one first.
The link from CJ_London also has some nice suggestions.

Thanks all, I will carry on and, when I will not forget, post my solution when I reach one.

 

[Rene vK]

as promised,

to solve my problem I created an new textbox on the form. This new field is called in the opening SQL statement (concate of Tag & Function). My search string is adressing this new field and the result is showing the right records.

Thx all for the input, Rene