Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Wildcard Character in a combo Seach Box

Posted on 2006-06-05
12
Medium Priority
?
278 Views
Last Modified: 2008-02-26
I am trying to use a combo box to find specifc strings in a text field.  For example I amy be looking for an HP 980 Printer but the item could be HP 980 or Hewlet-PAckard 980 or 980 Deskjet.  I want the combo box to be able to find any of these items and list them rather then finding the text as it is typed from left to right.

0
Comment
Question by:gordonmann
  • 5
  • 5
  • 2
12 Comments
 
LVL 3

Expert Comment

by:llessurt
ID: 16839658
This VB will populate the list box all the records that include ALL search terms in ANY specified fields:

selectquery = "SELECT printermodel, printermake, printerdescription FROM printers WHERE "
selectquery = selectquery & " AND ("
  arr = SPLIT(searchstring, " ")
  FOR i=0 to UBOUND(arr)
    selectquery = selectquery & "((printermodel LIKE '%" & arr(i) & "%') OR (printermake LIKE '%" & arr(i) & "%') OR (printerdescription LIKE '%" & arr(i) & "%')) AND "
    NEXT
selectquery = LEFT(selectquery, LEN(selectquery) - 5)
selectquery = selectquery & ")"

rs.OPEN selectquery
DO WHILE NOT rs.EOF
  response.write "<option>" & rs("printermodel") & "</option>"
rs.moveNext
LOOP
0
 
LVL 3

Expert Comment

by:llessurt
ID: 16839675
Line 2 needs edited to incude any predefined criteria
for example
  selectquery = selectquery & (quantityinstock > 0) AND ("

or delete it and add the opening "(" to the first line
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16839915
Which database?
what table definitions?

Example does in vba

assume table is
tblPrinters
PrinterID
Description


dim sFilter() as string
dim sSearch as string
dim sWhere as string
dim i as integer

sSearch = "HP 980 Printer"

sFilter = split(sSearch, " ")

sWhere = ""
for i = 0 to ubound(sFilter)
    if sWhere <> "" then sWhere = sWhere & " OR "
    swhere = swhere & "Description LIKE '*" & sFilter(i) & "*' "
next i

cboPrinters.RowSource = "SELECT PrinterID, Description FROM tblPrinters WHERE " & sWhere
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 65

Expert Comment

by:rockiroads
ID: 16839925
Note, the approach is similar to llessurt in that we both use SPLIT and we have both made assumptions on the tables
I have just given u the VBA alternative to work with MS Access

llessurt, pls do not think I am copying, I know VBA quite well and would of used split anyway as this is the only way to break up a delimited string. My intention was just to give the VBA alternative.

If u tell use what DB/Language and perhaps your printer table definition, that would help us to give u a more correct solution
0
 
LVL 1

Author Comment

by:gordonmann
ID: 16890433
I am finally getting back to this - sorry for the delay.  

table = tInventory
Combo Box = cboItemDesc
Bound Column in Combo = InventoryId
Only Search Field = txtItemDesc

I think I understand both approaches but I have to admit rockiroads seems to make more sense to me.  However, where do you put all the other code not in the combo box Row Source?  I tried to put it all there and I really did not like it at all.

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16891901
Ok, how do u want this approached?

At the moment, we set a string called "sSearch"
Then based on what that is, the combo printer list has its rowsource modified accordinly

So I guess you can modify it to use txtItemDesc

Say u have a button called cmdFind, u use this to load the printers

private sub cmdFind_Click

    dim sFilter() as string
    dim sSearch as string
    dim sWhere as string
    dim i as integer

    If IsNull(me.txtItemDesc)=True then
         msgbox "No search string specified"
         exit sub
    End if
    sSearch = txtItemDesc

    sFilter = split(sSearch, " ")

    sWhere = ""
    for i = 0 to ubound(sFilter)
        if sWhere <> "" then sWhere = sWhere & " OR "
        swhere = swhere & "Description LIKE '*" & sFilter(i) & "*' "
    next i

    cboItemDesc.RowSource = "SELECT InventoryID, Description FROM tblInventory WHERE " & sWhere
end sub



What do u mean by

"where do you put all the other code not in the combo box Row Source"

0
 
LVL 1

Author Comment

by:gordonmann
ID: 16893453
Noiw I am getting confused.  I am looking to use this in a combo/list box and your appear to be talking about a command button.  This seems like two items.  Are you saying I need a text box AND command button that will then populate the list box.  (Simmiliar to IIF)
0
 
LVL 1

Author Comment

by:gordonmann
ID: 16899158
After looking at this further I understand what you are suggesting.  However, my goal (although it may be unacheivable) is to type into the combo box (like any other search) but have it search for the string anywhere in the text.  

I full understand how to do what you suggest.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16900101
It would be easier to do thru a prompt (txtbox/cmdbtn for example)

My understanding was you wanted the combo filtered, but obviously not.

What you are saying is enter any partial text and try find it in the combo. That might be tricky.
u can type letters in but it finds based on first letter only
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1500 total points
ID: 16900120
u could try this

ensure the combo's limit to list is set to yes
create a notinlist event handler that looks something like this
now enter some text, one word only though
it will try find the first matching entry


Private Sub cboPrinters_NotInList(NewData As String, Response As Integer)

    Dim i As Integer

   
    For i = 0 To cboPrinters.ListCount - 1
        Debug.Print UCase$(cboPrinters.Column(0, i)), UCase$(NewData)
        If InStr(1, UCase$(cboPrinters.Column(0, i)), UCase$(NewData)) > 0 Then
            cboPrinters.Value = cboPrinters.ItemData(i)
        End If
    Next i

    Response = acDataErrContinue
   
   
End Sub



0
 
LVL 1

Author Comment

by:gordonmann
ID: 16906603
I am going to try a command button to open a search form in Modal mode that will hopefully take the vaule back to the correct line in the details part of the form.  It appears a combo box is just too difficult
0
 
LVL 1

Author Comment

by:gordonmann
ID: 16928159
Even though I did not choose to persue either of the offered suggestions it is only fair to reward the efforts.  Thank you to bioth.  Ultimately it was not feasible to put the effort in so I use a seperate modal form to "carry" he value back.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 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