Tackling arrays

[Ken Warthen]

I have a form that will be used to establish criteria for a report. Among
the controls on the form are a multi-select combobox with a list of two
character state abbreviations. I'm planning to use the selected state(s) in
a SQL statement as the record source for my report. Since the user may
select one or any number of states, my thoughts are to add each selection as
an element of an array and then use the array to build my SQL statement. My
issue is I've never worked with arrays and am not sure how to go about this,
or if this is even the best approach. Any advice will be greatly appreciated.

Ken

 

[Rick Brandt]

I have a form that will be used to establish criteria for a report.
Among
the controls on the form are a multi-select combobox with a list of
two character state abbreviations. I'm planning to use the selected
state(s) in a SQL statement as the record source for my report.
Since the user may select one or any number of states, my thoughts
are to add each selection as an element of an array and then use the
array to build my SQL statement. My issue is I've never worked with
arrays and am not sure how to go about this, or if this is even the
best approach. Any advice will be greatly appreciated.

Ken

No need for an array because the ListBox already exposes a collection of all
of the selected rows. It is called "ItemsSelected". You can just loop
through that. See the help topic for details.

 

[Albert D. Kallal]

As mentioned, I don't see any reason to put the selecitons to a array and
THEN add to sql.

just use code.....

If Me.lstOptions.ItemsSelected.Count > 0 Then
For Each MySelection In Me.lstOptions.ItemsSelected
If MySql <> "" Then
MySql = MySql & ","
End If
MySql = MySql & Me.lstOptions.Column(0, MySelection)
Next MySelection
MySql = "(MyOption in (" & MySql & "))"
End If
End If

' if no selections...assume all....

if MySql <> "" then
MySql = "select * from tblCustoemrs where " & MySql
else
MySql = "select * from tblCustomers"
end if