Obtaining Realtime Query result from two sets of criteria

[WSF]

Access97
I have a parts query form that has a PartNo textbox [txtPartNo] and
PartsDescription textbox, where I enter partial (or all) characters of a
part number or description in the appropriate textbox, the OnChange event
then generating the RowSource for a list box in real time. As more
characters are added the ListBox adjusts to list the decreasing number of
Part Numbers from the table [Stock], as well as their descriptions etc.
With the OnChange on each of the Part Number and Description textboxes
(separately) I have the following code (this being the one for the PartNo -
the Part Description is the same except that it works on the description
field in the [Stock] table. This code I have used from contributions from
this forum some time ago.

Dim strPartSQL As String
txtPartNoSearchString = Me![txtPartNo].Text

strPartSQL = "SELECT DISTINCTROW Stock.PartNo, Stock.Description,
Stock.Details, Stock.Group, Stock.Bin FROM Stock "
strPartSQL = strPartSQL & "WHERE ((Stock.PartNo) Like '*" &
txtPartNoSearchString & "*') "
strPartSQL = strPartSQL & "ORDER BY Stock.PartNo"

Me!lstResults.RowSource = strPartSQL
Me!lstResults.Requery
Dim ListControl As Control
Set ListControl = Me!lstResults
With ListControl
Me!txtRecordCount = .ListCount - 1
'If .ListCount < 8 Then

' .ListRows = .ListCount
'Else
' .ListRows = 8
'End If
End With

This works brilliantly.
What I would like to do is be able to enter partial entries in BOTH fields
to further refine the search.

e.g. put say "1234" (being a partial part number) in the parts textbox and
then say "batt" in the description textbox to list all "1234 with batt".
Even better would be the ability to add a third field [Group] as part of the
search.

I have tried to extend the SQL call but I seem to get into trouble when
either textbox is blank.

Any help gratefully appreciated.

WSF

 

[Douglas J. Steele]

Try the following:

Dim strWhere As String

strPartSQL = "SELECT DISTINCTROW Stock.PartNo, Stock.Description,
Stock.Details, Stock.Group, Stock.Bin FROM Stock "
If Len(txtPartNoSearchString & "") > 0 Then
strWhere = "WHERE Stock.PartNo Like '*" &
txtPartNoSearchString & "*' "
End If
If Len(txtDescSearchString & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND Stock.Description Like '*" &
txtDescSearchString & "*' "

Else
strWhere = "WHERE Stock.Description Like '*" &
txtDescSearchString & "*' "
End If
End If
strPartSQL = strPartSQL & strWhere
strPartSQL = strPartSQL & "ORDER BY Stock.PartNo"