Need code to input parameters for query

[chuq2001]

I have a report that uses 13 subreports. They all use the same query as a base, and I don't want to have to enter the parameters 13 times. I think I can store the parameters in a variable array and load them into the query each time it runs. I can't however find a good way to get the parameters into the array. I need some expertise and guildance, thanks.

Chuck

 

[scriverb]

ACCESS cannot access variables in SQL queries except through the use a Function statement. So, for this situation just create a Global variable for each in a database module along with a Function to call it. Then reference the Function Call in the criteria line in each query.

Example:
Global vName as string
Function Name()
Name = vName
End Function

Now in the query you can use the Function to capture the Global variable value and make the appropriate criteria comparison to the data in a table.

Example:
Select tblClients.Name, tblClients.Address, tblClients.City, tblClients.State
FROM tblClients
WHERE tblClients.Name = Name();

The values of the global variables must first be set in your code prior to running the queries.

Example:
vName = "Smith"
DoCmd.OpenQuery "qryClientAddresses"


These global variables can then be used by all of your queries and query the same records for information.

Bob Scriver

 

[oharab]

Have a look at faq701-1964 for a variation/extension of Bobs method.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[chuq2001]

Great, I've got the variable declared and working, however, calling the function to retrieve variable in the SQL works until I put in a Between clause, any suggestions

 

[scriverb]

Cut and paste the SQL WHERE portion of the query. The between statement with the functions should work just fine.

Let me take a look at it.

Bob Scriver

 

[chuq2001]

Bob
Thanks, I got the whole of it working well along with the between clause. I appriciate all your help, I may need some more!

Chuck