Getting the "IN FUNCTION" data from a form

[Aietoe]

Hi,
i'm trying to run a query using the "in function" in the criteria area.
My problem occur because i want the "In function" data to come from a form.
Here is my query:

SELECT DISTINCTROW PROD_I_SMOD_SR_ACTE_MD_NOMTF.SMOD_NO_DISP, PROD_I_SMOD_SR_ACTE_MD_NOMTF.SMOD_COD_ACTE, PROD_I_SMOD_SR_ACTE_MD_NOMTF.SMOD_DAT_SERV
FROM PROD_I_SMOD_SR_ACTE_MD_NOMTF
WHERE (((PROD_I_SMOD_SR_ACTE_MD_NOMTF.SMOD_COD_ACTE) In ([Forms]![xxx]![Selection])) AND ((PROD_I_SMOD_SR_ACTE_MD_NOMTF.SMOD_DAT_SERV)=#7/10/2001#));

When running the query, the selection entered in the form may look like this:
6903;6928;9668 or
224;34;5678 or
87346;988;00495

When i run the query entering manually the criteria «IN (224;34;5678)» or any other selections, it works fine.
It's when i try to get those selections from a form that i get the error.

Any Idea?

Thanks Aietoe

 

[Quehay]

Can you predetermine how many possible values for the IN list that you'll have/allow? If so, why not put dedicated boxes for each possible value.

Then you'd build the "WHERE IN (..." string by:

1. Testing the box for a value

2. Add the value to the IN list and append a comma

3. Strip the extra comma from the last value.

If you're a real code junkie you could dynamically add boxes to the form from a "Add another criterion" command button. Use the tag property to identify these boxes, then use the Ctrls collection to get the boxes with the appropriate tag, etc. (If this doesn't make sense then you wouldn't want to jump in that deep.)

There's an MS Knowledge Base article on creating a function to use in queries that will accept a flexible number of parameters and create the IN list accordingly. I don't remember the Q# but just search for "Parameter IN list", and you should find it. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[Aietoe]

Hi Jeff,

Thanks a lot for your suggestions.
As a matter a fact, i'm not a «real code junkie», so, i will not get into that «Add another criterion».

While i was waiting for an answer, i kept on going with a «dedicated boxes» solution. The only thing is that i must limit myself to about 12 criterions.

I thought there was an easy way to let my clients enter there choices separated by «;», so that's why i asked for your help.
But i guess that «easy solution» is quite rare with Access.

Anyway, 12 criterions should be enought, so i'll continue in that direction.
Thanks a lot again.

Bye!

Aietoe