Handy routine for building an IN clause from multi-select listbox

[DFS]

You might also find other uses

================================================== =======
Public Function getSelections(selType As String, selSeparator As
Boolean, selList As ListBox, selCol As Byte) As String

'BUILD IN CLAUSE FROM ITEMS CHOSEN IN MULTI-SELECT LIST BOX

'ARGUMENTS
'selType = text or number
'selSeparator: true returns apostrophes between items
'selList = name of listbox control
'selCol = which column is the data found in

getSelections = ""
dim ctl as Control, i as integer

Set ctl = selList
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) = True Then
If selType = "text" Then
If selSeparator = True Then
getSelections = getSelections & "'" & ctl.Column(selCol, i) & "',"
Else
getSelections = getSelections & "" & ctl.Column(selCol, i) & ","
End If
ElseIf selType = "number" Then
getSelections = getSelections & ctl.Column(selCol, i) & ","
End If
End If
Next i

getSelections = Left(getSelections, Len(getSelections) - 1)

End Function
================================================== =======

[Wayne Gillespie]

On Thu, 26 Jan 2006 02:20:03 -0500, DFS <nospam@dfs_.com> wrote:

You might also find other uses

================================================= ========
Public Function getSelections(selType As String, selSeparator As
Boolean, selList As ListBox, selCol As Byte) As String

'BUILD IN CLAUSE FROM ITEMS CHOSEN IN MULTI-SELECT LIST BOX

'ARGUMENTS
'selType = text or number
'selSeparator: true returns apostrophes between items
'selList = name of listbox control
'selCol = which column is the data found in

getSelections = ""
dim ctl as Control, i as integer

Set ctl = selList
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) = True Then
If selType = "text" Then
If selSeparator = True Then
getSelections = getSelections & "'" & ctl.Column(selCol, i) & "',"
Else
getSelections = getSelections & "" & ctl.Column(selCol, i) & ","
End If
ElseIf selType = "number" Then
getSelections = getSelections & ctl.Column(selCol, i) & ","
End If
End If
Next i

getSelections = Left(getSelections, Len(getSelections) - 1)

End Function
================================================= ========

Nice work.
I would make one suggestion however. I would make the separator a quote " rather than an apostrophe.
The apostrophe may cause errors in your IN cluse if the list box contains an entry like O'Reilly.

Wayne Gillespie
Gosford NSW Australia

[DFS]

Wayne Gillespie wrote:

On Thu, 26 Jan 2006 02:20:03 -0500, DFS <nospam@dfs_.com> wrote:

You might also find other uses

================================================ =========
Public Function getSelections(selType As String, selSeparator As
Boolean, selList As ListBox, selCol As Byte) As String

'BUILD IN CLAUSE FROM ITEMS CHOSEN IN MULTI-SELECT LIST BOX

'ARGUMENTS
'selType = text or number
'selSeparator: true returns apostrophes between items
'selList = name of listbox control
'selCol = which column is the data found in

getSelections = ""
dim ctl as Control, i as integer

Set ctl = selList
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) = True Then
If selType = "text" Then
If selSeparator = True Then
getSelections = getSelections & "'" & ctl.Column(selCol, i) & "',"
Else
getSelections = getSelections & "" & ctl.Column(selCol, i) & ","
End If
ElseIf selType = "number" Then
getSelections = getSelections & ctl.Column(selCol, i) & ","
End If
End If
Next i

getSelections = Left(getSelections, Len(getSelections) - 1)

End Function
================================================ =========

Nice work.
I would make one suggestion however. I would make the separator a quote " rather than an apostrophe.
The apostrophe may cause errors in your IN cluse if the list box contains an entry like O'Reilly..

Good suggestion, and I will implement it. I actually haven't ever had
that problem in an IN clause, because I almost always use ID numbers.
Having said that I'm sure tomorrow morning it'll pop up.

And when I said 'selList = name of listbox control' that's incorrect.
You actually pass in the listBox object, of course, not the name.

Usage example:

whereStr = whereStr & "AND Location IN (" & getSelections("text", True,
Me.listLocation, 0) & ") "

[Terry Kreft]

What about if it's measurements e.g. 3'4".

Whatever you use as the string delimiter just double up any occurences in
the string, so your original.
getSelections = getSelections & "'" & ctl.Column(selCol, i) & "',"
becomes
getSelections = getSelections & "'" & Replace(ctl.Column(selCol, i),
"'", "''", compare:=vbTextCompare) & "',"

That is replace all single instances of an apostrophe with two instances of
an apostrphe.
--

Terry Kreft
"DFS" <nospam@dfs_.com> wrote in message
news:1Z******************@bignews4.bellsouth.net.. .

Wayne Gillespie wrote:

On Thu, 26 Jan 2006 02:20:03 -0500, DFS <nospam@dfs_.com> wrote:

You might also find other uses

================================================ =========
Public Function getSelections(selType As String, selSeparator As
Boolean, selList As ListBox, selCol As Byte) As String

'BUILD IN CLAUSE FROM ITEMS CHOSEN IN MULTI-SELECT LIST BOX

'ARGUMENTS
'selType = text or number
'selSeparator: true returns apostrophes between items
'selList = name of listbox control
'selCol = which column is the data found in

getSelections = ""
dim ctl as Control, i as integer

Set ctl = selList
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) = True Then
If selType = "text" Then
If selSeparator = True Then
getSelections = getSelections & "'" & ctl.Column(selCol, i) & "',"
Else
getSelections = getSelections & "" & ctl.Column(selCol, i) & ","
End If
ElseIf selType = "number" Then
getSelections = getSelections & ctl.Column(selCol, i) & ","
End If
End If
Next i

getSelections = Left(getSelections, Len(getSelections) - 1)

End Function
================================================ =========

Nice work.
I would make one suggestion however. I would make the separator a quote " rather than an apostrophe. The apostrophe may cause errors in your IN cluse if the list box

contains an entry like O'Reilly..

Good suggestion, and I will implement it. I actually haven't ever had
that problem in an IN clause, because I almost always use ID numbers.
Having said that I'm sure tomorrow morning it'll pop up.

And when I said 'selList = name of listbox control' that's incorrect.
You actually pass in the listBox object, of course, not the name.

Usage example:

whereStr = whereStr & "AND Location IN (" & getSelections("text", True,
Me.listLocation, 0) & ") "

[David W. Fenton]

DFS <nospam@dfs_.com> wrote in
news:1Z******************@bignews4.bellsouth.net:

Wayne Gillespie wrote:

I would make one suggestion however. I would make the separator a
quote " rather than an apostrophe. The apostrophe may cause
errors in your IN cluse if the list box contains an entry like
O'Reilly..

Good suggestion, and I will implement it. I actually haven't ever
had that problem in an IN clause, because I almost always use ID
numbers. Having said that I'm sure tomorrow morning it'll pop up.

Don't you need to check the data type of the column you're putting
the criteria on? That is, if you're using IN on a numeric column,
you want no quotes, if on a text column, you want them.

You could, theoretically, pass one of the Access constants for data
type to your routine and use it to choose to quote the strings or
not.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Wayne Gillespie]

On Thu, 26 Jan 2006 11:21:55 -0600, "David W. Fenton" <XX*******@dfenton.com.invalid> wrote:

DFS <nospam@dfs_.com> wrote in
news:1Z******************@bignews4.bellsouth.ne t:

Wayne Gillespie wrote:

I would make one suggestion however. I would make the separator a
quote " rather than an apostrophe. The apostrophe may cause
errors in your IN cluse if the list box contains an entry like
O'Reilly..

Good suggestion, and I will implement it. I actually haven't ever
had that problem in an IN clause, because I almost always use ID
numbers. Having said that I'm sure tomorrow morning it'll pop up.

Don't you need to check the data type of the column you're putting
the criteria on? That is, if you're using IN on a numeric column,
you want no quotes, if on a text column, you want them.

You could, theoretically, pass one of the Access constants for data
type to your routine and use it to choose to quote the strings or
not.

The OP's function has a SelType argument to indicate whether the data is text ot numeric.
Wayne Gillespie
Gosford NSW Australia

[David W. Fenton]

Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:dl********************************@4ax.com:

On Thu, 26 Jan 2006 11:21:55 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

DFS <nospam@dfs_.com> wrote in
news:1Z******************@bignews4.bellsouth.net :

Wayne Gillespie wrote:

I would make one suggestion however. I would make the separator
a quote " rather than an apostrophe. The apostrophe may cause
errors in your IN cluse if the list box contains an entry like
O'Reilly..

Good suggestion, and I will implement it. I actually haven't
ever had that problem in an IN clause, because I almost always
use ID numbers. Having said that I'm sure tomorrow morning it'll
pop up.

Don't you need to check the data type of the column you're putting
the criteria on? That is, if you're using IN on a numeric column,
you want no quotes, if on a text column, you want them.

You could, theoretically, pass one of the Access constants for
data type to your routine and use it to choose to quote the
strings or not.

The OP's function has a SelType argument to indicate whether the
data is text ot numeric.

What data? The data from the listbox? That won't necessarily map
correctly to the data type of the underlying field that you're
writing your WHERE clause against. Numbers might very well be stored
in a text field, for instance.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[DFS]

David W. Fenton wrote:

What data? The data from the listbox? That won't necessarily map
correctly to the data type of the underlying field that you're
writing your WHERE clause against. Numbers might very well be stored
in a text field, for instance.

???

While you're busy sorting that out, just pass in the argument "number"
when querying a number field and "text" when querying a text field.

[DFS]

Terry Kreft wrote:

What about if it's measurements e.g. 3'4".
There's no end to the what-ifs.

The only data handling feature I added beyond the basic number/text
choice is an option to return text data without separators, which is
more suitable for showing on screen or a report.

Whatever you use as the string delimiter just double up any occurences in
the string, so your original.
getSelections = getSelections & "'" & ctl.Column(selCol, i) & "',"
becomes
getSelections = getSelections & "'" & Replace(ctl.Column(selCol, i),
"'", "''", compare:=vbTextCompare) & "',"

That is replace all single instances of an apostrophe with two instances of
an apostrphe.

[Terry Kreft]

You seem to have missed the point of my post.

Changing the text delimiter from apostrophe to double quote doesn't remove
the problem caused by the delimiter being embedded in the string you are
delimiting.
--

Terry Kreft
"DFS" <nospam@dfs_.com> wrote in message
news:ME****************@bignews1.bellsouth.net...

Terry Kreft wrote:

What about if it's measurements e.g. 3'4".

There's no end to the what-ifs.

The only data handling feature I added beyond the basic number/text
choice is an option to return text data without separators, which is
more suitable for showing on screen or a report.

Whatever you use as the string delimiter just double up any occurences in the string, so your original.
getSelections = getSelections & "'" & ctl.Column(selCol, i) & "',"
becomes
getSelections = getSelections & "'" & Replace(ctl.Column(selCol, i),
"'", "''", compare:=vbTextCompare) & "',"

That is replace all single instances of an apostrophe with two instances of an apostrphe.