No results when using AND in Select query

[BH]

Hi, I have two txt boxes that I use to filter down a list of projects. The results are shown in a listBox. No results are shown when I use criteria in both boxes (the else statement below). Individual boxes work fine when the other box is empty.

Can anyone see why the else part of my code will not pull back the required records? Everything else appears to work fine!

If IsNull(txtRefFind.Value) And IsNull(txtTitleFind.Value) Then
'pull back all projects
lstResults.RowSource = "select * from tblPortfolio order by Ref_No"

ElseIf IsNull(txtTitleFind.Value) And txtRefFind.Value <> "" Then
'pull back only those where Ref Number partially match
lstResults.RowSource = "select * from tblPortfolio where Ref_No like '*" & txtRefFind.Value & "*'"

ElseIf IsNull(txtRefFind.Value) And txtTitleFind.Value <> "" Then
'pull back only those where Title partially match
lstResults.RowSource = "select * from tblPortfolio where Study_Title like '*" & txtTitleFind.Value & "*'"

Else
'pull back those where Ref Number and Title partially match
lstResults.RowSource = "select tblPortfolio.* from tblPortfolio where tblPortfolio.Ref_No Like '*" & txtRefFind.Value & "*' And where tblPortfolio.Study_Title Like '*" & txtTitleFind.Value & "*'"

End If


Thanks

BH

 

[PWise]

 lstResults.RowSource = "select tblPortfolio.* from tblPortfolio where tblPortfolio.Ref_No Like '*" & txtRefFind.Value & "*'  And [s]where[/s]  tblPortfolio.Study_Title Like '*" & txtTitleFind.Value & "*'"

no need for second where

 

[PHV]

Furthermore, you may simplify like this:

Dim strSQL As String
strSQL = "SELECT * FROM tblPortfolio WHERE 1=1"
If Trim(txtRefFind.Value & "") <> "" Then
  strSQL = strSQL & " AND Ref_No Like '*" & txtRefFind.Value & "*'"
End If
If Trim(txtTitleFind.Value & "") <> "" Then
  strSQL = strSQL & " AND Study_Title Like '*" & txtTitleFind.Value & "*'"
End If
lstResults.RowSource = strSQL & " ORDER BY Ref_No"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

BH . . .

Although there's more lines involved, I always design my code for [blue]maximum readibility to me![/blue] Example:

[blue]   Dim RS As Property, RF As Boolean, TF As Boolean
   
   RS = Me!lstResults.Properties("RowSource")
   RF = Trim(Me!txtRefFind & "") = ""
   TF = Trim(Me!txtTitleFind & "") = ""
   
   If RF And TF Then
      RS = "SELECT * " & _
           "FROM tblPortfolio " & _
           "ORDER BY [Ref_No];"
   ElseIf Not RF And TF Then
      RS = "SELECT * " & _
           "FROM tblPortfolio " & _
           "WHERE [Ref_No] like '*" & Me!txtRefFind & "*';"
   ElseIf RF And Not TF Then
      RS = "SELECT * " & _
           "FROM tblPortfolio " & _
           "WHERE [Study_Title] like '*" & Me!txtTitleFind & "*';"
   Else
      RS = "SELECT tblPortfolio.* " & _
           "FROM tblPortfolio " & _
           "WHERE [Ref_No] Like '*" & Me!txtRefFind & "*' And " & _
                 "[Study_Title] Like '*" & Me!txtTitleFind & "*';"
   End If

   Set TF = Nothing
   Set RF = Nothing
   Set RS = Nothing[/blue]

[blue]Your Thougts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

BH . . .

So sorry. In my prior post:

[blue]RS = Me!lstResults.Properties("RowSource")
   Should Be . . .
[purple][b]SET[/b][/purple] RS = Me!lstResults.Properties("RowSource")[/blue]

. . . and at the end there should only be:

[blue]   Set RS = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[BH]

Brilliant! thank you very much everyone.

pwise - well spotted, I could have stared at that all day (nearly did!) and still have missed it.

PHV and TheAceMan1 - great examples of different styles. The simplified method suits me as my typing is so slow!! but for readibility and future referencing TheAceMan1 style is something I will try out.

Thanks again for your help and ideas

BH