report parameter from listbox

[leahf via AccessMonster.com]

There exists a report that includes names of study places. The same study
place can appear more than once in the study place table since each one can
be in another settlement.
e.g. study place id 100 is study place A in settlement 10.
study place id 101 is study place A in settlement 20.

The settlement is of no interest in this report. The user would like to
filter the report using only a parameter of study place (i.e. the parameter
list would use distinct study place).

The parameter is from a multi-select listbox. I have a defined function that
gets the string of the chosen study places.

I defined a function to get the selected study places from the listbox. I
figured that in the query of the report there can be an expression that
checks if the study place is in the string of study places that were selected.
In actuality, if I have one study place chosen, it works. More than that the
report is empty.

A couple of points:
1) While it works if one study place is chosen, it does not work if I put
quotation marks around the study place. I would think that a string would
need quotation marks.

2) To complicate things, some of the study place names have a single
quotation mark ('), some have a double ("). The quotation marks do not
hinder the report if one study group is chosen.

Am I going about this in the right way? And how can I get the report
choosing more than one study place?

Thanks.
Leah

 

[Ken Snell \(MVP\)]

See my sample database here:
Building SQL string based on values entered into controls
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

It shows how to build an SQL Where clause from a multiselect listbox to use
for filtering a report that you're opening.

 

[leahf via AccessMonster.com]

I was just writing to you that I had originally begun with the where clause
but it wasn't working out ("syntax error (missing operator)". After reading
some of the samples that you recommended I began to play around with it again
and was about to take a break from all this (to clear my head) when it dawned
upon me that I used a where clause in the query itself in order to get the
one study place...

I dropped that extra where clause in the query and everything works perfectly.
The quotation marks are no problem either.

Thank you so very much.
Leah

See my sample database here:
Building SQL string based on values entered into controls
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

It shows how to build an SQL Where clause from a multiselect listbox to use
for filtering a report that you're opening.

There exists a report that includes names of study places. The same study
place can appear more than once in the study place table since each one

[quoted text clipped - 34 lines]

Thanks.
Leah