Parameter Query run from VBA

G

Guest

I have an Access 2000 stored query that I am trying to set criteria for in
VBA before running. I know I can create the whole query and criteria in VBA
using SQL statements but because of the number of fields involved I'm hoping
I can do this by making it a stored query instead. This will make it easier
to maintain later.

The criteria consists of a dynamic array of FileIDs. So the WHERE clause
should look something like:

"Where [FileID]=" & aryID(1) & " Or [FileID]=" & aryID(2) & " Or "etc...

I thought about using a parameter query and assigning the parameter value in
VBA but I can't figure out how to make the "OR" criteria for an undetermined
amount of FileIDs.

Any help is greatly appreciated.
 
M

Michel Walsh

Hi,


Use a temporary table to store the aryID (one value per record). Say it is
called temp, with its only field called aryID. Make the single field a
primary key (no null allowed, no duplicated entry allowed). Then


SELECT myTable.*
FROM myTable INNER JOIN temp
ON myTable.FileID = temp.aryID


will do the job.



Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Thank you for the response. That sounds like it should work.
--
Jean


Michel Walsh said:
Hi,


Use a temporary table to store the aryID (one value per record). Say it is
called temp, with its only field called aryID. Make the single field a
primary key (no null allowed, no duplicated entry allowed). Then


SELECT myTable.*
FROM myTable INNER JOIN temp
ON myTable.FileID = temp.aryID


will do the job.



Hoping it may help,
Vanderghast, Access MVP




Jean said:
I have an Access 2000 stored query that I am trying to set criteria for in
VBA before running. I know I can create the whole query and criteria in
VBA
using SQL statements but because of the number of fields involved I'm
hoping
I can do this by making it a stored query instead. This will make it
easier
to maintain later.

The criteria consists of a dynamic array of FileIDs. So the WHERE clause
should look something like:

"Where [FileID]=" & aryID(1) & " Or [FileID]=" & aryID(2) & " Or "etc...

I thought about using a parameter query and assigning the parameter value
in
VBA but I can't figure out how to make the "OR" criteria for an
undetermined
amount of FileIDs.

Any help is greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top