×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Array of parameters in access query

Array of parameters in access query

Array of parameters in access query

(OP)
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

RE: Array of parameters in access query

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

RE: Array of parameters in access query

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close