i got your idea and tried this manner.
i placed an input box and have the user place his search criteria separated by comma. the program looks for the words in the fields place them in the array and use the sql like and serries of and. for a while this suffices. here is the code:
Dim findStr As String
Dim x As Integer
Dim DescAry() As String
Dim SQLStrg, SQLStrg1 As String
Dim n, m, R, q, p As Integer
Dim Desc As String
On Error GoTo err
If dcboCat1.Text = "Category" Or dcboSubCat1.Text = "SubCategory" Then
MsgBox "Please enter the correct category and sub-category. Thank you.", vbOKOnly, "Special Search"
Title = "Special Search"
findStr = InputBox("Please enter some description of " _
& " the products each separated by a comma. Thank you ", Title)
' read the entry from the input box
n = Len(Trim(findStr))
'check if "," is properly placed
q = 0
For m = 1 To n
If Mid(findStr, m, 1) = "," Then
q = q + 1
If Mid(findStr, 1, 1) = "," Then
MsgBox "Please correct the way description is written." & vbCrLf _
& "Comma should be written in between description. Thank you.", vbOKOnly, "Notice!"
m = 1
R = 1
p = 1
Desc = ""
Do Until m = n + 1 ' main sensor
Do Until Mid(findStr, m, 1) = "," ' forward sensor/counter (m)
m = m + 1
If m = n + 1 Then ' terminate loop
Desc = Mid(findStr, p, m - p)
ReDim Preserve DescAry(R)
DescAry(R) = Trim(Desc)
' trailer sensor/counter (p)
If m < (n + 1) Then
Do Until Mid(findStr, p, 1) = ","
p = p + 1
R = R + 1
m = m + 1
p = p + 1
SQLStrg1 = ""
For x = 1 To R
SQLStrg1 = SQLStrg1 + " AND DESCRIPTION LIKE '*" & DescAry(x) & "*'"
SQLStrg = "SELECT * FROM ITEMS WHERE Category = '" & dcboCat1.Text & "' AND SubCatName = '" & dcboSubCat1 & "'"
SQLStrg = SQLStrg + SQLStrg1
Data1.RecordSource = SQLStrg
If Data1.Recordset.NoMatch = True Then
MsgBox "There is/are no product/s available " _
& " with said description. "
Label3.Caption = Data1.Recordset.RecordCount
MsgBox "There is no record for that description. Thank you.", vbOKOnly, "Special Search"
Label3.Caption = "0"
thank you again.