Array of parameters in access query

[Durkin]

Does anyone know is it possible to have a parameter in a query that is an array?
The background is that I want to do a crosstab query where the column headings are dates from a transactions table. As there are transactions going in every day, it would take forever to run the query normally so I want to be able to pass in a list of dates of variable length instead. If passing in an array is not possible any other suggestions would be appreciated. Thanks. Durkin

 

[ImpMan]

The only thing I can suggest is to pass an array to a function that returns a string which is a SQL statement. The function deconstructs the array automatically. The sql string returned should then be used to change the query def of the query you want to run. Not elegant but effective. (the limit of a SQL statments is 64,000 chars so there is plently of scope there!)

Hope this helps

 

[jimmythegeek]

OPTION 1:

Set a Global string equal to your array (let's call it gstrArray)

Next copy this into a module:
=====
Public Function GetStringArray() as String
GetStringArray = gstrArray
End Function
=====

Now in the criteria of your query try this:

IN(GetStringArray())

OPTION 2

Either create a form with a to and from date, and put the following in the criteria:

Between Forms!YourForm!txtFrom AND Forms!YourForm!txtTo

Or

Simply put this in the criteria:

Between [Enter Start Date] AND [Enter Ending Date]

This will give you parameter boxes that pop up where you can enter a start date and end date. Jim Lunde
compugeeks@hotmail.com
Custom Application Development