Multiple results in one field

[chrisaabb]

Hi

I would be grateful for help as this one has me stumped. I have created a query with a number of fields, one of which is a list of organisation unit numbers. I want the user to be able to select more than one organisation unit number and then for all the records that match that org unit no to be returned.

I have managed to create a parameter box for one organisation unit number which comes up 'Enter the org. unit' number' which returns all records that match that org unit number, but I then want the user to have the option to enter a second org unit number (and possibly up to 6 different numbers) and then all the results to be returned.

This query is to be used as a search facility, eg the org unit numbers entered are always likely to be different so any pointers would be gratefully received.

Thanks


Chris

 

[Sergeant]

Take a look at this.

 

[chrisaabb]

Update

Hi

Thanks for your reply. Unless I am missing something this doesn't work as users will be entering different org unit numbers each time eg the first time they might want to find all records in 12345 and 23456 and the next time on 34567 and 45678 etc. There are over 1,500 org unit numbers so it is not a practical option to have all these numbers in a list box for multiple selection.

Any other ideas?

Thanks again


Chris

 

[Pat Hartman]

You can't do this with a popup parameter box. You'll need to write VBA to create an SQL string. This method has been posted. Your code needs to gather the multiple values and place them in an In(...) clause. The examples usually show a multi-select list box but you can use whatever method you want to collect the parameters.

 

[chrisaabb]

Help

Pat

Thank you so much for your reply. Unfortunately I do not know how to write code so could you post the link to the answer you refer to and I will try and work it out from that.

Thanks

Chris

 

[Pat Hartman]

It is unlikely that you will find an example that does exactly what you want. Sargeant posted a link to an example that shows how to create an In(...) predicate. You'll need to interpret the code so you get the values from someplace other than a multi-select listbox.

There is no time like the present to teach yourself VBA. Try searching this forum for In( and you should find more code samples. Perhaps there will be one that you can understand.

 

[chrisaabb]

Thank You

Pat and Sargeant

Thank you both very much for your help and for your patience. By looking at the example posted by Sargeant and searching on here again and finding the following link on here under another thread http://support.microsoft.com/default.aspx?scid=kb;EN-US;100131 I have managed to adapt the code supplied and got it to work.

When I retire next year I will learn VBA as I will have more time but in the meantime thanks again.

Chris