multi-select list box in sql statement issue

  • Thread starter Thread starter Cyberwolf
  • Start date Start date
C

Cyberwolf

I have a module that builds a sql statement to run a select query. I am
having issues with the code that builds the In clause from the list box. All
it is placing is the comma between each item selected in the list box. I
know at one time you could only used numerics in an In statement but I
thought that after office 97 this was set to accept alpha/numerics. Here is
the code I am using

Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Dim strWhere1 As String


strSQL = "Select * from tblSegmentOutput_Test"

strWhere = strWhere & " WHERE [QuoteNumber] = " & "'" & Me.txtQuote &
"'" & _
" AND [Revision] = " & "'" & Me.txtRevision & "'" & _
" AND [LineNumber] = " & "'" & Me.txtLine & "'"

For Each varItem In Me.Controls!txtSegments.ItemsSelected
strWhere1 = strWhere1 & Me.Controls!txtSegments.ItemData(varItem) &
", "
Next varItem

If Len(strWhere) > 0 Then
strSQL = strSQL & strWhere & " AND [SegmentName] In (" & _
Left$(strWhere1, Len(strWhere1) - 2) & ")"

End If
Here is the output from strSQL

Select * from tblSegmentOutput_Test WHERE [QuoteNumber] = 'S-18859' AND
[Revision] = '0' AND [LineNumber] = '1' AND [SegmentName] In (, , , )

In this case I had 4 items selected. I only get nulls on this line

? Me.Controls!txtSegments.ItemData(varItem)
Null

TIA
 
Cyberwolf said:
I have a module that builds a sql statement to run a select query. I am
having issues with the code that builds the In clause from the list box.
All
it is placing is the comma between each item selected in the list box. I
know at one time you could only used numerics in an In statement but I
thought that after office 97 this was set to accept alpha/numerics. Here
is
the code I am using

Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Dim strWhere1 As String


strSQL = "Select * from tblSegmentOutput_Test"

strWhere = strWhere & " WHERE [QuoteNumber] = " & "'" & Me.txtQuote &
"'" & _
" AND [Revision] = " & "'" & Me.txtRevision & "'" & _
" AND [LineNumber] = " & "'" & Me.txtLine & "'"

For Each varItem In Me.Controls!txtSegments.ItemsSelected
strWhere1 = strWhere1 & Me.Controls!txtSegments.ItemData(varItem) &
", "
Next varItem

If Len(strWhere) > 0 Then
strSQL = strSQL & strWhere & " AND [SegmentName] In (" & _
Left$(strWhere1, Len(strWhere1) - 2) & ")"

End If
Here is the output from strSQL

Select * from tblSegmentOutput_Test WHERE [QuoteNumber] = 'S-18859' AND
[Revision] = '0' AND [LineNumber] = '1' AND [SegmentName] In (, , , )

In this case I had 4 items selected. I only get nulls on this line

? Me.Controls!txtSegments.ItemData(varItem)
Null

TIA
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf
Click to expand...

There doesn't look to be anything wrong with the code, but one thing did
strike me as a little peculiar: isn't txtSegments a rather strange name for
a listbox? Are you sure you're referring to the correct control?
 
Yes I am . I originally had this as a text box, then changed it to a list
box and I didn't want to correct all my other code. So there you have it.

I find it odd that it shows as nulls. The list box is based on a query that
gets updated based on 2 other txt boxes. I don;t see how that would affect
as I have those 2 text files in the upper portion of the strWhere and they
pull OK. They are txtQuote & txtRevision.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


Stuart McCall said:
Cyberwolf said:
I have a module that builds a sql statement to run a select query. I am
having issues with the code that builds the In clause from the list box.
All
it is placing is the comma between each item selected in the list box. I
know at one time you could only used numerics in an In statement but I
thought that after office 97 this was set to accept alpha/numerics. Here
is
the code I am using

Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Dim strWhere1 As String


strSQL = "Select * from tblSegmentOutput_Test"

strWhere = strWhere & " WHERE [QuoteNumber] = " & "'" & Me.txtQuote &
"'" & _
" AND [Revision] = " & "'" & Me.txtRevision & "'" & _
" AND [LineNumber] = " & "'" & Me.txtLine & "'"

For Each varItem In Me.Controls!txtSegments.ItemsSelected
strWhere1 = strWhere1 & Me.Controls!txtSegments.ItemData(varItem) &
", "
Next varItem

If Len(strWhere) > 0 Then
strSQL = strSQL & strWhere & " AND [SegmentName] In (" & _
Left$(strWhere1, Len(strWhere1) - 2) & ")"

End If
Here is the output from strSQL

Select * from tblSegmentOutput_Test WHERE [QuoteNumber] = 'S-18859' AND
[Revision] = '0' AND [LineNumber] = '1' AND [SegmentName] In (, , , )

In this case I had 4 items selected. I only get nulls on this line

? Me.Controls!txtSegments.ItemData(varItem)
Null

TIA
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf
Click to expand...

There doesn't look to be anything wrong with the code, but one thing did
strike me as a little peculiar: isn't txtSegments a rather strange name for
a listbox? Are you sure you're referring to the correct control?
Click to expand...
 
I figured out the issue. I had my bound column set to the wrong column.
When I fixed that it works fine.

Thanks for your help anyways
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


Stuart McCall said:
Cyberwolf said:
I have a module that builds a sql statement to run a select query. I am
having issues with the code that builds the In clause from the list box.
All
it is placing is the comma between each item selected in the list box. I
know at one time you could only used numerics in an In statement but I
thought that after office 97 this was set to accept alpha/numerics. Here
is
the code I am using

Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String
Dim strWhere1 As String


strSQL = "Select * from tblSegmentOutput_Test"

strWhere = strWhere & " WHERE [QuoteNumber] = " & "'" & Me.txtQuote &
"'" & _
" AND [Revision] = " & "'" & Me.txtRevision & "'" & _
" AND [LineNumber] = " & "'" & Me.txtLine & "'"

For Each varItem In Me.Controls!txtSegments.ItemsSelected
strWhere1 = strWhere1 & Me.Controls!txtSegments.ItemData(varItem) &
", "
Next varItem

If Len(strWhere) > 0 Then
strSQL = strSQL & strWhere & " AND [SegmentName] In (" & _
Left$(strWhere1, Len(strWhere1) - 2) & ")"

End If
Here is the output from strSQL

Select * from tblSegmentOutput_Test WHERE [QuoteNumber] = 'S-18859' AND
[Revision] = '0' AND [LineNumber] = '1' AND [SegmentName] In (, , , )

In this case I had 4 items selected. I only get nulls on this line

? Me.Controls!txtSegments.ItemData(varItem)
Null

TIA
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf
Click to expand...

There doesn't look to be anything wrong with the code, but one thing did
strike me as a little peculiar: isn't txtSegments a rather strange name for
a listbox? Are you sure you're referring to the correct control?
Click to expand...
 
Back
Top