IN Criteria built from a List Box

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

In Access 200 mdb: I have a list box on a form that is
set for multiple selections. The List Box control source
is a list of about 100 Group Codes. I want the user to be
able to select as many group codes as they like to
include in a report.
I loop thru the list box building a string of each
selection seperated by a comma and place the string in a
hidden text box on the form.
My report query then has the criteria for the group code
as IN ([Forms]![frmDonationByGroup]![txtGrp]).
Why won't this work?
I can do betweens, equal to ... using a reference to a
form control but can't get the IN to work.
I tried typing different syntax into the text box,
(with / without quotes, commas, parens...) but never got
it to work.
Thanks
Chuck
 
Chuck,

Instead of doing that, why don't you cut the SQL of your query and paste it
into your code, then you can concatenate the text to the In clause. Then
you can write it back to the querydef

Dim strSQL as string
Dim qdf as DAO.Querydef

strSQL = "SELECT * FROM YourTable " _
& "WHERE ID IN (" & me.txtGrp & ")"
Set qdf = Currentdb.Querydef("YourQueryName")
qdf.SQL = strSQL
set qdf = Nothing

BTW, if the field you are trying to put in the IN ( ) clause is a text
field, each value must be wrapped in quotes.

HTH
Dale
 
Back
Top