Array as a query parameter??

[EvilCabal]

Hi, I need a query thats shows specific records from a table. However, the key field values that determine what records are to be shown in the query are stored in an array. I know I could create a temp. table with the values in the array and then build my query from this table but isn't there a more 'optimized' way to do it, without the temp. table? Thanx!

 

[oharab]

You will need to create the SQL statement for the query dynamicallly:

strSQL="SELECT * FROM tblData WHERE tblData.ID IN (" & Join(vArray,",&quot & ""

the Join function turns a 1 Dimensional array into a character seperated string.

You can then use strSQL and save it as a querydef or use it from code to open a recordset, whatever you want.

BTW the Join function is A2k+ only. If you want an equivalent function for A97, then let me know.


HTH

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

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

 

[EvilCabal]

Hey, thanx a lot, works perfect. But at first I had an error because the join function returned a string terminated with a ",". I simply removed it using the left function but I was wondering if this was normal because you haven't used it in you exemple. Did I miss something?

 

[EvilCabal]

Sorry about the last post, my array had an empty cell at the end. My mistake