build the where clause...

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Greetings:

I am attempting to modify Allen Browne's Boolean Search Code as follows. A
"where clause" is built by concatenating records in a list box (lbxChosen) to
and added to an SQL statement to define the rowsource of lbxSource. The code
works fine so long as there is only 1 item in lbxChosen. When a second
criteria for the where clause is added, the SQL statement produces no records
(even though I know there are records with both search criteria used).

The code is as follows:

Private Sub cboMeSH_AfterUpdate()
'filter lbxSource based on the MeSH terms in lbxChosen

'determine how many records there are in lbxChosen
intListCount = [lbxChosen].ListCount - 1

'loop through lbxChosen to contruct the where statement
For intLoopCounter = 0 To intListCount
strWhere = strWhere & "Where (tblMesh.chrMeshID = """ &
Me.lbxChosen.Column(0, intLoopCounter) & """) AND "
Next intLoopCounter

'Chop off the trailing " AND "
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
strSQL = "SELECT DISTINCT tblSource.idsSourceID, tblSource.chrTitle " & _
"FROM tblSource INNER JOIN tblMeSH ON tblSource.idsSourceID = "
& _
"tblMeSH.intSourceID " & _
strWhere & ";"

Me.lbxSource.RowSource = strSQL
End If
End Sub

With one criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248")
- and this works fine

With 2 criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248") AND Where (tblMesh.chrMeshID =
"C14.240.400")
- this produces no records in lbxSource when I know there should be

I'm having trouble figuring out why the code works for one criteria but not
2 or more? I wonder if it has something to do with the AND statement and
quotations?
Thanks for any help.
 
On Tue, 13 Oct 2009 17:42:01 -0700, Steve

Paste the resulting sqlstatement in a new blank query in sql view, and
switch to design view. Access will point out the problem.
In your case: you have the word "where" multiple times. It can occur
only once. One way to learn the syntax is to create a query in design
view, and then switch to sql view to find out what you should be
generating.
I would use an IN clause rather than concatenation with AND, but
that's a personal preference for brevity, not a syntax issue.

-Tom.
Microsoft Access MVP
 
Looks like you are adding a 'where' clause for every item instead of just the
first.
For intLoopCounter = 0 To intListCount
strWhere = strWhere & "Where (tblMesh.chrMeshID = """ &
Click to expand...
It might help to display the SQL for your query just before you execute it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


Steve said:
Greetings:

I am attempting to modify Allen Browne's Boolean Search Code as follows. A
"where clause" is built by concatenating records in a list box (lbxChosen) to
and added to an SQL statement to define the rowsource of lbxSource. The code
works fine so long as there is only 1 item in lbxChosen. When a second
criteria for the where clause is added, the SQL statement produces no records
(even though I know there are records with both search criteria used).

The code is as follows:

Private Sub cboMeSH_AfterUpdate()
'filter lbxSource based on the MeSH terms in lbxChosen

'determine how many records there are in lbxChosen
intListCount = [lbxChosen].ListCount - 1

'loop through lbxChosen to contruct the where statement
For intLoopCounter = 0 To intListCount
strWhere = strWhere & "Where (tblMesh.chrMeshID = """ &
Me.lbxChosen.Column(0, intLoopCounter) & """) AND "
Next intLoopCounter

'Chop off the trailing " AND "
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
strSQL = "SELECT DISTINCT tblSource.idsSourceID, tblSource.chrTitle " & _
"FROM tblSource INNER JOIN tblMeSH ON tblSource.idsSourceID = "
& _
"tblMeSH.intSourceID " & _
strWhere & ";"

Me.lbxSource.RowSource = strSQL
End If
End Sub

With one criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248")
- and this works fine

With 2 criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248") AND Where (tblMesh.chrMeshID =
"C14.240.400")
- this produces no records in lbxSource when I know there should be

I'm having trouble figuring out why the code works for one criteria but not
2 or more? I wonder if it has something to do with the AND statement and
quotations?
Thanks for any help.
Click to expand...
 
Steve,

Replace the "AND" in:

Me.lbxChosen.Column(0, intLoopCounter) & """) AND "

With an "OR"

Your where clause is trying to identify records where chrMeshID = "xxx" AND
"yyy", which will always return 0 records. When you replace the "AND" in the
where clause, you will also need to make the appropriate changes in the rest
of the code to deal with "OR" instead of "And".
 
Thanks! That's exactly what I was looking for.
--
Steve


Klatuu said:
You code will not work. Rather than explain why, here is a function that
will take care of it:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

--
Dave Hargis, Microsoft Access MVP


Steve said:
Greetings:

I am attempting to modify Allen Browne's Boolean Search Code as follows. A
"where clause" is built by concatenating records in a list box (lbxChosen) to
and added to an SQL statement to define the rowsource of lbxSource. The code
works fine so long as there is only 1 item in lbxChosen. When a second
criteria for the where clause is added, the SQL statement produces no records
(even though I know there are records with both search criteria used).

The code is as follows:

Private Sub cboMeSH_AfterUpdate()
'filter lbxSource based on the MeSH terms in lbxChosen

'determine how many records there are in lbxChosen
intListCount = [lbxChosen].ListCount - 1

'loop through lbxChosen to contruct the where statement
For intLoopCounter = 0 To intListCount
strWhere = strWhere & "Where (tblMesh.chrMeshID = """ &
Me.lbxChosen.Column(0, intLoopCounter) & """) AND "
Next intLoopCounter

'Chop off the trailing " AND "
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
strSQL = "SELECT DISTINCT tblSource.idsSourceID, tblSource.chrTitle " & _
"FROM tblSource INNER JOIN tblMeSH ON tblSource.idsSourceID = "
& _
"tblMeSH.intSourceID " & _
strWhere & ";"

Me.lbxSource.RowSource = strSQL
End If
End Sub

With one criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248")
- and this works fine

With 2 criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248") AND Where (tblMesh.chrMeshID =
"C14.240.400")
- this produces no records in lbxSource when I know there should be

I'm having trouble figuring out why the code works for one criteria but not
2 or more? I wonder if it has something to do with the AND statement and
quotations?
Thanks for any help.
Click to expand...
Click to expand...
 
You code will not work. Rather than explain why, here is a function that
will take care of it:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

--
Dave Hargis, Microsoft Access MVP


Steve said:
Greetings:

I am attempting to modify Allen Browne's Boolean Search Code as follows. A
"where clause" is built by concatenating records in a list box (lbxChosen) to
and added to an SQL statement to define the rowsource of lbxSource. The code
works fine so long as there is only 1 item in lbxChosen. When a second
criteria for the where clause is added, the SQL statement produces no records
(even though I know there are records with both search criteria used).

The code is as follows:

Private Sub cboMeSH_AfterUpdate()
'filter lbxSource based on the MeSH terms in lbxChosen

'determine how many records there are in lbxChosen
intListCount = [lbxChosen].ListCount - 1

'loop through lbxChosen to contruct the where statement
For intLoopCounter = 0 To intListCount
strWhere = strWhere & "Where (tblMesh.chrMeshID = """ &
Me.lbxChosen.Column(0, intLoopCounter) & """) AND "
Next intLoopCounter

'Chop off the trailing " AND "
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
strSQL = "SELECT DISTINCT tblSource.idsSourceID, tblSource.chrTitle " & _
"FROM tblSource INNER JOIN tblMeSH ON tblSource.idsSourceID = "
& _
"tblMeSH.intSourceID " & _
strWhere & ";"

Me.lbxSource.RowSource = strSQL
End If
End Sub

With one criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248")
- and this works fine

With 2 criteria debug.print strWhere yields:
Where (tblMesh.chrMeshID = "C14.280.067.248") AND Where (tblMesh.chrMeshID =
"C14.240.400")
- this produces no records in lbxSource when I know there should be

I'm having trouble figuring out why the code works for one criteria but not
2 or more? I wonder if it has something to do with the AND statement and
quotations?
Thanks for any help.
Click to expand...
 
Back
Top