Results 1 to 5 of 5

Thread: Search command

  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: Search command

    I am trying to educate myself in the search command function with below code and trying my best to intrepret this.

    //In the form module, declaring strSQL & strTemp both as string
    Private Sub cmdSearch_Click()
    Dim strSQL As String
    Dim strTemp As String

    //Evaluating the search by combo box & setting its focus
    If CmbSearchBy.Text <> C_Search2 Then
    MsgBox "Select From The List.", , "System Message"
    CmbSearchBy.SetFocus
    Exit Sub
    End If

    Me.MousePointer = vbHourglass

    //I do not understand this point forward
    strSQL = "SELECT %COLDOCNO AS %COLDOCNOALIAS " & _
    " FROM %TABLE "
    Select Case CmbSearchBy.Text

    Case C_Search2
    strSQL = strSQL & " WHERE (%COLDOCNO LIKE '%" & CSQLQuote(txtSearch) & "%' )"
    End Select
    strSQL = strSQL & " GROUP BY %COLDOCNO ORDER BY %COLDOCNO ASC"

    strSQL = Replace(strSQL, "%COLDOCNOALIAS", IIf(strColDocNoAlias <> "", strColDocNoAlias, strColDocNo))

    strSQL = Replace(strSQL, "%COLDOCNO", strColDocNo)

    strSQL = Replace(strSQL, "%TABLE", strTableName)

    If rst.State = adStateOpen Then rst.Close
    rst.Open strSQL, ConnACCPAC, adOpenForwardOnly, adLockReadOnly

    Set DataGrid1.DataSource = rst

    Me.MousePointer = vbNormal

    On Error GoTo RecordCount_Handler
    LblRecordCount.Caption = "Total found: " & rst.RecordCount

    Exit Sub

    RecordCount_Handler:
    LblRecordCount.Caption = "???"
    End Sub

    Thank you for any help.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    //I do not understand this point forward
    strSQL = "SELECT %COLDOCNO AS %COLDOCNOALIAS " & _
    " FROM %TABLE "
    Select Case CmbSearchBy.Text
    
    Case C_Search2
    strSQL = strSQL & " WHERE (%COLDOCNO LIKE '%" & CSQLQuote(txtSearch) & "%' )"
    End Select
    strSQL = strSQL & " GROUP BY %COLDOCNO ORDER BY %COLDOCNO ASC"
    
    MsgBox(strSQL)
    
    strSQL = Replace(strSQL, "%COLDOCNOALIAS", IIf(strColDocNoAlias <> "", strColDocNoAlias, strColDocNo))
    
    strSQL = Replace(strSQL, "%COLDOCNO", strColDocNo)
    
    strSQL = Replace(strSQL, "%TABLE", strTableName)
    MsgBox(strSQL)
    run it with the added lines (highlighted) and it should make a bit more sense. It builds a dynamic SQL string and then uses the replace command to substitute some text with values for, e.g. table name.

    Ugly way of doing things... I don't like it
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2008
    Posts
    7
    you're right. it does look that way. maybe i wasnt being precise for my question. here goes.

    in my form i have a command button (Search), combo box, textbox & a datagrid. I would like to make a search function and the searching are based on the criteria from the combo box (which is by ITEMNO & DESCRIPTION). which means the user can choose either one. the system should be able to do the searching by both ways, meaning the user can either type in the item / description full or partial name then. when the Search button is click, the result will be displayed in the datagrid.

    I am doing this using the ADODB recordset connection string, as my database are store in a sql server.

    im doing a fresh new one as oppose to the previous one as its quite 'messy'..meantime, any idea how i can do this in the simpliest way possible?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The basic SQL you're looking for once full constructed is below (where "abc" is the search term entered).
    Like comparisons allow for wildcard searches and depending on which dbms you are using, percentage or asterisks symbols are your wildcard symbols.
    Code:
    SELECT itemno
         , description
    FROM   myTable
    WHERE  itemno Like '%abc%'
    OR       description Like '%abc%'
    Hope this helps
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2008
    Posts
    7
    Thank you so much for this..im goin to expand it to achieve the full searching that i wanted..at least im into the right direction now, will keep you posted. Thanks again georgev!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •