where in query misbehaving

M

madEG

Registered User.
Local time
Today, 18:03
Joined
Jan 26, 2007
Messages
307
Hello,

I have a multi select listbox that is used to add id's to a query. Specifically:

Code: 
SELECT dbo_tblTaggedItems.TaggedItemID, dbo_tblItems.ItemDescription, '   ' AS [space], dbo_tblTags.TagDescription
FROM (dbo_tblTaggedItems INNER JOIN dbo_tblItems ON dbo_tblTaggedItems.ItemID = dbo_tblItems.ItemID) INNER JOIN dbo_tblTags ON dbo_tblTaggedItems.TagID = dbo_tblTags.TagID
WHERE dbo_tblTags.TagID in ( [forms].[frmTagItems].[txtstrWhere].value );
...in the where clause at the end, the items selected in the listbox are added to a string, a textbox is set to the value of that string (for display purposes) and then this text box's string/value is used in the where in list...

But it doesn't work well for some reason. When a single record is selected, effectively giving a single value in the "where in" clause, this works just fine.

...and further, if I hard code multiple values into the query:

Code: 
SELECT dbo_tblTaggedItems.TaggedItemID, dbo_tblItems.ItemDescription, '   ' AS [space], dbo_tblTags.TagDescription
FROM (dbo_tblTaggedItems INNER JOIN dbo_tblItems ON dbo_tblTaggedItems.ItemID = dbo_tblItems.ItemID) INNER JOIN dbo_tblTags ON dbo_tblTaggedItems.TagID = dbo_tblTags.TagID
WHERE dbo_tblTags.TagID in ( 6,7,8);
...that works fine too.

But for some reason, when building the additional n'th records to the string it fails to return any records at all.
icon5.gif


The mdb is running against SQL Server as the backend. The id's are integers, and teh building code is:

Code: 
        Dim strWhere As String
        Dim ctl As Control
        Dim varItem As Variant
        
        'add selected values to string
        Set ctl = Me.lstTags
        For Each varItem In ctl.ItemsSelected
          strWhere = strWhere & ctl.ItemData(varItem) & ","
        Next varItem
        
        'trim trailing comma
        strWhere = Left(strWhere, Len(strWhere) - 1)
        txtstrWhere = strWhere
Boy, I am stumped!

Thoughts?
 
This should work, right? This is driving me crazy...

If I close the form, and run the query, and am asked to set the value for the txtstrWhere, and give it a single digit, say "5", it works fine - so doubt its the query - or some problem with conversion of str to int.

But if I give it two values, say "5,6" it doesn't work...

Further, if I hard code the where clause to be "...where in (5,6);" in the query everything runs as it should and I get records returned.

What else can this be? This is crazy...
 
Slight modification:

Code: 
        Dim strWhere As String
        Dim ctl As Control
        Dim varItem As Variant
        
        'add selected values to string
        Set ctl = Me.lstTags
        [COLOR="Red"]strWhere = "In("[/COLOR]
        For Each varItem In ctl.ItemsSelected
          strWhere = strWhere & ctl.ItemData(varItem) & ","
        Next varItem
        
        'trim trailing comma
        strWhere = Left(strWhere, Len(strWhere) - 1)
        txtstrWhere = strWhere [COLOR="red"]& ")"[/COLOR]
 
Hello Bob,

Code: 
[FONT=monospace]WHERE dbo_tblTags.TagID In ([forms].[frmTagItems].[txtstrWhere])
[/FONT]

I have the above already in the query, or so I thought by having the parentheses around the "[forms].[frmTagItems].[txtstrWhere]" -- do those parentheses not get used as the "enclosures" for the in list?

(I tried your way, and it actually showed all teh records, rather than none... which was just as weird) heh.

Is there some way I can msgbox the finished query before it is being sent to the backend?
 
Use either Debug.Print or MsgBox to get the text of your query. I prefer Debug.Print because the entire string appears in the Immediate window of your VB editor and is easy to cut and paste.
 
I've not been able to build a string to do what you are showing. I use a dynamic SQL Statement usually and replace the where clause with basically what I showed in my code change.
 
Ok, just in case my frustration is not allowing me to be clear... (which wouldn't be the first time...)

I took a screen shot of what I am basically doing... and am including the full onclick() routine for the form's list box...

Code: 
Private Sub lstTags_Click()

    Select Case Me!frameTagdataReviewData
        Case 2
        
        Dim strWhere As Variant
        Dim ctl As Control
        Dim varItem As Variant
        
        'add selected values to string
        Set ctl = Me.lstTags
        For Each varItem In ctl.ItemsSelected
          strWhere = strWhere & CInt(ctl.ItemData(varItem)) & ","
        Next varItem
        
        'trim trailing comma
        strWhere = Left(strWhere, Len(strWhere) - 1)
        txtstrWhere = strWhere
        
        'MsgBox ("txtstrWhere:'" & txtstrWhere & "'")
        
        lstTaggedItems.RowSource = "qrySelectTaggedItems_basedOnSelectedTag"
        lstTaggedItems.Requery
    End Select

End Sub

The list box in the top right is what I have set on multi select (and the routine I basically took from pbaldy's site) that I use to value a string that I use in the query, as shown by the red. The value of the string I have being placed in the text box (also outlined in red).

Am I going about this the wrong way? When I have only one record selected it works fine - it is just when I have multiples that it barfs.
 

Attachments

  • build_where_in_list.jpg
    build_where_in_list.jpg
    92.9 KB · Views: 111
This is helpful. So, what do you need to do with the resulting query? I ask because I think we can add in the query into the code here but I need to know what you are going to use the query for.
 
The query is being used to return records for the bottom listbox. per the attached screen shots...

When I select a record in the top right, it pulls the associated records from a bridge table into the bottom listbox: attached exampleA.jpg and exampleB.jpg shows that there are child records for the parent records in the top right... This is fine...

But when I select two records in the top right, it returns nothing (exampleC.jpg) even though when I hard code the values in the query it does work (exampleD.jpg, and exampleE.jpg)... so the data is there - it seems that access doesn't like my multi valued "where in" clause...

Arg.
 

Attachments

  • exampleA.jpg
    exampleA.jpg
    84.6 KB · Views: 110
  • exampleB.jpg
    exampleB.jpg
    86 KB · Views: 107
  • exampleC.jpg
    exampleC.jpg
    86.8 KB · Views: 116
  • exampleD.jpg
    exampleD.jpg
    87.6 KB · Views: 117
  • exampleE.jpg
    exampleE.jpg
    101.2 KB · Views: 110
Okay, instead of using the query itself, we are going to use the code and assign it to the listbox:
Code: 
Private Sub lstTags_Click()

    Select Case Me!frameTagdataReviewData
        Case 2
        
        Dim strWhere As Variant
        Dim ctl As Control
        Dim varItem As Variant
        [COLOR="Red"]Dim strSQL As String[/COLOR]

        'add selected values to string
        Set ctl = Me.lstTags
        [COLOR="red"]strWhere = "WHERE dbo_tblTags.TagID In("[/COLOR]
        For Each varItem In ctl.ItemsSelected
          strWhere = strWhere & CInt(ctl.ItemData(varItem)) & ","
        Next varItem
        
        'trim trailing comma
        strWhere = Left(strWhere, Len(strWhere) - 1)
        txtstrWhere = strWhere [COLOR="red"]& ")"[/COLOR]
        
        'MsgBox ("txtstrWhere:'" & txtstrWhere & "'")
       [COLOR="red"] strSQL =[/COLOR] "SELECT dbo_tblTaggedItems.TaggedItemID, dbo_tblItems.ItemDescription, '   ' AS [space], dbo_tblTags.TagDescription " & _
                 "FROM (dbo_tblTaggedItems INNER JOIN dbo_tblItems ON dbo_tblTaggedItems.ItemID = dbo_tblItems.ItemID) " & _
                 "INNER JOIN dbo_tblTags ON dbo_tblTaggedItems.TagID = dbo_tblTags.TagID " & _
	         [COLOR="red"]strWhere & ";"[/COLOR]
        lstTaggedItems.RowSource = [COLOR="red"]strSQL[/COLOR]

    End Select

End Sub
 
Wow. At 4:58PM!!!

I set up a msg to see what was happening, and I made a tiny change... but you got me 99% there. I was getting in my own way sticking to the <robot_voice> Must use query... </robot_voice> way to do this.

Final code:

Code: 
Private Sub lstTags_Click()

    Select Case Me!frameTagdataReviewData
        Case 2
        
        Dim strWhere As Variant
        Dim ctl As Control
        Dim varItem As Variant
        Dim strSQL As String
        
        'add selected values to string
        Set ctl = Me.lstTags
        strWhere = "WHERE dbo_tblTags.TagID in ("
        For Each varItem In ctl.ItemsSelected
          strWhere = strWhere & CInt(ctl.ItemData(varItem)) & ","
        Next varItem
        
        'trim trailing comma
        strWhere = Left(strWhere, Len(strWhere) - 1)
        txtstrWhere = strWhere
        
        'MsgBox ("txtstrWhere:'" & txtstrWhere & "'")
        
        'lstTaggedItems.RowSource = "qrySelectTaggedItems_basedOnSelectedTag"
        'lstTaggedItems.Requery
        strSQL = "SELECT dbo_tblTaggedItems.TaggedItemID, dbo_tblItems.ItemDescription, '   ' AS [space], dbo_tblTags.TagDescription " & _
                 "FROM (dbo_tblTaggedItems INNER JOIN dbo_tblItems ON dbo_tblTaggedItems.ItemID = dbo_tblItems.ItemID) " & _
                 "INNER JOIN dbo_tblTags ON dbo_tblTaggedItems.TagID = dbo_tblTags.TagID " & _
             strWhere & ");"
        MsgBox ("strSQL:^" & strSQL & "^")
        lstTaggedItems.RowSource = strSQL
        lstTaggedItems.Requery
    End Select

End Sub

Bob: Thanks for sticking it through with me! I really appreciate it! :)

And Access_Hero, I need to look into the debug workings, my skills using a debugger consist of adding a zillion msgbox's ;) -- woefully inadequate!
 
Bob, It seems I can't give you any more rep points... But, attached... ;)
 

Attachments

  • YouRockHand.jpg
    YouRockHand.jpg
    32.7 KB · Views: 108
You must log in or register to reply here.

Similar threads

S
Problem with sql syntax with JOIN and WHERE in vba
Replies
8
Views
1,118
Ken Sheridan
Ken Sheridan
A
Error 3828 when creating a CSV from a query
2 3
Replies
42
Views
1,460
andymartin3186
A
K
Solved Using dynamic sql instead of saved query in Insert Into queries...
Replies
17
Views
1,501
The_Doc_Man
The_Doc_Man
G
Setting up Sql Search to Sql Server backend using Connections
Replies
4
Views
282
Geirr
G
N
Solved How get lid of domain function and replace them with opening recordset in MS Access VBA
Replies
10
Views
508
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom