//I do not understand this point forward
strSQL = "SELECT %COLDOCNO AS %COLDOCNOALIAS " & _
" FROM %TABLE "
Select Case CmbSearchBy.Text
strSQL = strSQL & " WHERE (%COLDOCNO LIKE '%" & CSQLQuote(txtSearch) & "%' )"
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)
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.
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?
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.
WHERE itemno Like '%abc%'
OR description Like '%abc%'