function to loop through a multi select list box

[Bob Wickham]

Hello,

I have been adapting some code written by Albert D Kallal to build a
Where string from a multi-select list box.

The code compiles but when I click the button I get an error.
Run-time error: 3075
Syntax error (missing operator)in query expression 'WHERE [Lender] IN
('6'))'

Clicking the DeBug button highlights this line in the Sub

DoCmd.OpenReport "rptCommissionReceivedByDate", acViewPreview, , strWhere


If nothing is selected in the list box it works fine and displays all
the records in the report.

Can anybody see where I'm going wrong with this

Bob Wickham

------------------------------------------------------------

Private Sub cmdPreview2_Click()

Dim strWhere As String

strWhere = BuildWhere()

DoCmd.OpenReport "rptCommissionReceivedByDate", acViewPreview, ,
strWhere
End Sub


Private Function BuildWhere() As String

Dim strWhere As String, strIN As String

' create the IN string by looping thru the listbox
' where "lstLender" is the name of the list box on the form
For i = 0 To lstLender.ListCount - 1
If lstLender.Selected(i) Then
If lstLender.Column(0, i) = "All" Then
strIN = ""
Exit For
End If
If strIN <> "" Then strIN = strIN & ","
strIN = strIN & "'" & lstLender.Column(0, i) & "'"
End If
Next i

If strIN <> "" Then
strWhere = " WHERE [Lender] IN (" & strIN & ")"
End If

BuildWhere = strWhere
End Function

 

[George Nicholson]

1) Per Help: the "Where" argument of OpenReport:
"A string expression that's a valid SQL WHERE clause without the word
WHERE."
I'm guessing the original code was intended to build a WHERE string for an
SQL query, not the Where argument of a report/form.
So, you need to do a little more adapting...
strWhere = " WHERE [Lender] IN (" & strIN & ")"
will probably work as:
strWhere = "[Lender] IN (" & strIN & ")"

2) I don't see where it is in the code, but the error message you posted
indicates that you may have one too many closing parenthesis. Then again,
error messages have been known to contain typos (so have newsgroup postings)
This is probably not a concern.

3) Is Lender a text field or numeric? If numeric, you'll probably get a type
mismatch error once you fix your WHERE problem.
If that happens, you'll probably need to change
strIN = strIN & "'" & lstLender.Column(0, i) & "'"
to
strIN = strIN & lstLender.Column(0, i)


HTH,

 

[Bob Wickham]

1) Per Help: the "Where" argument of OpenReport:
"A string expression that's a valid SQL WHERE clause without the word
WHERE."
I'm guessing the original code was intended to build a WHERE string for an
SQL query, not the Where argument of a report/form.
So, you need to do a little more adapting...
strWhere = " WHERE [Lender] IN (" & strIN & ")"
will probably work as:
strWhere = "[Lender] IN (" & strIN & ")"

2) I don't see where it is in the code, but the error message you posted
indicates that you may have one too many closing parenthesis. Then again,
error messages have been known to contain typos (so have newsgroup postings)
This is probably not a concern.

3) Is Lender a text field or numeric? If numeric, you'll probably get a type
mismatch error once you fix your WHERE problem.
If that happens, you'll probably need to change
strIN = strIN & "'" & lstLender.Column(0, i) & "'"
to
strIN = strIN & lstLender.Column(0, i)


HTH,

Hi George,

Yes the code was written by Albert Kallal who responded to my post about
3 years ago on how to change a multi parameter query to a series of
multi-select list boxes.
I couldn't figure it out then and I still can't.

I changed the line as you suggested but I can't tell if it made any
difference because I'm still getting the same error message.

I have actually got a multiple multi-select thing working by following (
and then adding to ) Allen Brownes instructions on his website
http://allenbrowne.com/ser-50.html
but the Function idea of Alberts seemed like a much more efficient and
elegant way to go.

The little bit of programming I've studied has taught me that if it
seems like I'm writing the same code over and over again, then I really
need a function.

But I can't argue with code that works so I'll stick with what I have.

Thanks for your interest.

Bob

 

[Bob Wickham]

1) Per Help: the "Where" argument of OpenReport:
"A string expression that's a valid SQL WHERE clause without the word
WHERE."
I'm guessing the original code was intended to build a WHERE string
for an SQL query, not the Where argument of a report/form.
So, you need to do a little more adapting...
strWhere = " WHERE [Lender] IN (" & strIN & ")"
will probably work as:
strWhere = "[Lender] IN (" & strIN & ")"

2) I don't see where it is in the code, but the error message you
posted indicates that you may have one too many closing parenthesis.
Then again, error messages have been known to contain typos (so have
newsgroup postings) This is probably not a concern.

3) Is Lender a text field or numeric? If numeric, you'll probably get
a type mismatch error once you fix your WHERE problem.
If that happens, you'll probably need to change
strIN = strIN & "'" & lstLender.Column(0, i) & "'"
to
strIN = strIN & lstLender.Column(0, i)


HTH,

Hi George,

Yes the code was written by Albert Kallal who responded to my post about
3 years ago on how to change a multi parameter query to a series of
multi-select list boxes.
I couldn't figure it out then and I still can't.

I changed the line as you suggested but I can't tell if it made any
difference because I'm still getting the same error message.

I have actually got a multiple multi-select thing working by following (
and then adding to ) Allen Brownes instructions on his website
http://allenbrowne.com/ser-50.html
but the Function idea of Alberts seemed like a much more efficient and
elegant way to go.

The little bit of programming I've studied has taught me that if it
seems like I'm writing the same code over and over again, then I really
need a function.

But I can't argue with code that works so I'll stick with what I have.

Thanks for your interest.

Bob

It works,

Not sure what happened to the error message. I turned the computer off
while I had lunch and when I came back to it it worked.
And I've managed to get it to run through the next loop as well.

It seems like its a lot less coding than the other way I have it and I
particularly like how it defaults to "All" if nothing is selected.

Albert Kallal provided this solution to a problem about 3 years ago.
And I've just figured it out.
At this rate I'll be a MVP soon !!!

Thanks to you George and especially to Albert D. Kallal

Bob Wickham


Private Function BuildWhere() As String

Dim strWhere As String, strIN As String


' create the IN string by looping thru the listbox
' where "lstLender" is the name of the list box on the form
For i = 0 To lstLender.ListCount - 1
If lstLender.Selected(i) Then
If lstLender.Column(0, i) = "All" Then
strIN = ""
Exit For
End If
If strIN <> "" Then strIN = strIN & ","
strIN = strIN & lstLender.Column(0, i)
End If
Next i

If strIN <> "" Then
strWhere = "[LenderID] IN (" & strIN & ")"
End If

' and again to loop through the next list box on the form

strIN = ""
For i = 0 To lstCommissionType.ListCount - 1
If lstCommissionType.Selected(i) Then
If lstCommissionType.Column(0, i) = "All" Then
strIN = ""
Exit For
End If
If strIN <> "" Then strIN = strIN & ","
strIN = strIN & lstCommissionType.Column(0, i)
End If
Next i

If strIN <> "" Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[CommissionTypeID] IN (" & strIN & ")"
End If

BuildWhere = strWhere
End Function