Post #151,782
4/19/04 1:11:31 PM
|
How do I pass parameters to a Query in Access
At work, must use Access (2003).
I would like to give the users a form to generate their query. Select which day, which month, what system, etc.
Help doesn't seem to help. No books available (I'm not supposed to be programming). 'Twas a simple thing to do in Paradox. :-(
Any suggestions or pointers would be appreciated.
Joe
|
Post #151,795
4/19/04 1:40:00 PM
|
Re: How do I pass parameters to a Query in Access
There are two ways of going about this in Access that I know of. I havn't written any Access code in 2003, but this should still work.
The first is to create a query that points to the fields of the form that contain the criteria. This is the old school way in Access, but if your doing something simple you might want to go this way.
To do this create a form with the fields you need, and save it. Then create a query and in the criteria field of the query right click and select the build option. Buried somewhere among the options is one to select a field from an existing form.
Then you just need to stick a button on the form to run the query. In this case it is usually DoCmd.OpenQuery "name of query", some options I can't remember.
The other way is using VB code like this. Line 3, 4 and 5 just show common ways of referencing the parameters.
Set Query1 = db.QueryDefs("Name of Query")
Query1("State") = "PA"
Query1![City] = "Lancaster"
Query1.Parameters(2) = "17601"
Set RecordSet1 = Query1.OpenRecordset()
Jay
|
Post #151,811
4/19/04 2:06:16 PM
|
For *very* simple stuff
...you can reference a parameter which doesn't exist and Access will prompt you for it:
SELECT * FROM Things WHERE Year = [Enter a year]
...will prompt the user for the year, which obviates the need for all that Form crap for simple queries. IIRC you can even declare the type within an explicit PARAMETERS clause.
|
Post #151,822
4/19/04 3:20:01 PM
|
Here's the SQL code.
The qParms are what I need to pass into the query. SELECT Schedule.Level, Schedule.Job, Schedule.Schid, Schedule.System, [Trigger Jobs].StartTm, Schedule.TriggerBy, Schedule.NOEX, CommJobs.Comm INTO [Q-Day13 Schedule] FROM [Trigger Jobs] INNER JOIN (Schedule LEFT JOIN CommJobs ON Schedule.Job = CommJobs.Job) ON [Trigger Jobs].Link = Schedule.Link WHERE ((([Trigger Jobs].RunDays)=[qDOM]) AND (([Trigger Jobs].Months)="ALL" Or ([Trigger Jobs].Months) Like [qMonth]) AND (([Trigger Jobs].Calendar)="SP") AND (([Trigger Jobs].BusinessFunction)=[qSystem])) OR ((([Trigger Jobs].RunDays) Like [qAPDay]) AND (([Trigger Jobs].Months)="ALL" Or ([Trigger Jobs].Months) Like [qMonth]) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="M")) OR ((([Trigger Jobs].RunDays) Is Null) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="D")) OR ((([Trigger Jobs].RunDays) Not Like [qAPDay]) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="D")) OR ((([Trigger Jobs].RunDays) Like [qDOW] And ([Trigger Jobs].RunDays) Not Like [qExcept]) AND (([Trigger Jobs].Calendar)="AP" Or ([Trigger Jobs].Calendar)="DY") AND (([Trigger Jobs].BusinessFunction)=[qSystem]) AND (([Trigger Jobs].Frequency)="W")) OR ((([Trigger Jobs].RunDays) Like [qJDate]) AND (([Trigger Jobs].Calendar)="JU") AND (([Trigger Jobs].BusinessFunction)=[qSystem])) ORDER BY [Trigger Jobs].StartTm, Schedule.Key; Or the details of the Access code
RunDays |
Months |
Calendar |
Business Function |
Frequency |
[qDOM] |
\ufffdAll\ufffd or Like [qMonth] |
\ufffdSP\ufffd |
[qSystem] |
|
Like [qAPDay] |
\ufffdAll\ufffd or Like [qMonth] |
\ufffdAP\ufffd or \ufffdDY\ufffd |
[qSystem |
\ufffdM\ufffd |
Is Null |
|
\ufffdAP\ufffd or \ufffdDY\ufffd |
[qSystem] |
\ufffdD\ufffd |
Not Like [qAPDay] |
|
\ufffdAP\ufffd or \ufffdDY\ufffd |
[qSystem] |
\ufffdD\ufffd |
Like [qDOW] and Not Like [qExcept] |
|
\ufffdAP\ufffd or \ufffdDY\ufffd |
[qSystem] |
\ufffdW\ufffd |
like [qJDate] |
|
\ufffdJU\ufffd |
[qSystem] |
|
|
Post #151,825
4/19/04 3:53:17 PM
|
Reformatting a bit
And may not be Access compatible (i.e. alias for the table names): SELECT \n sch.Level, \n sch.Job, \n sch.Schid, \n sch.System, \n trg.StartTm, \n sch.TriggerBy, \n sch.NOEX, \n cmj.Comm INTO [Q-Day13 Schedule]\nFROM [Trigger Jobs] trg\n INNER JOIN (Schedule sch LEFT JOIN CommJobs cmj ON sch.Job = cmj.Job) ON trg.Link = sch.Link\nWHERE \n (\n (trg.BusinessFunction = [qSystem]) AND\n (trg.RunDays = [qDOM]) AND \n (trg.Calendar = "SP") AND\n (trg.Months = "ALL" OR trg.Months Like [qMonth])\n ) OR (\n (trg.BusinessFunction = [qSystem]) AND \n (trg.RunDays Like [qAPDay]) AND \n (trg.Calendar IN("AP", "DY")) AND \n (trg.Months = "ALL" OR trg.Months Like [qMonth]) AND \n (trg.Frequency = "M")\n ) OR (\n (trg.BusinessFunction = [qSystem]) AND \n (trg.RunDays Is Null) AND \n (trg.Calendar IN("AP", "DY")) AND \n (trg.Frequency = "D")\n ) OR (\n (trg.BusinessFunction = [qSystem]) AND \n (trg.RunDays Not Like [qAPDay]) AND \n (trg.Calendar IN("AP", "DY")) AND \n (trg.Frequency = "D")\n ) OR (\n (trg.BusinessFunction = [qSystem]) AND \n (trg.RunDays Like [qDOW] And trg.RunDays Not Like [qExcept]) AND \n (trg.Calendar IN("AP", "DY")) AND \n (trg.Frequency = "W")\n ) OR (\n (trg.BusinessFunction = [qSystem]) AND\n (trg.RunDays Like [qJDate]) AND \n (trg.Calendar = "JU")\n )\nORDER BY trg.StartTm, sch.Key;
|
Post #151,830
4/19/04 4:40:43 PM
|
Are all of those date parts from a single date?
That is, if you can derive qDOM, qMonth, qAPDay, qDOW, and qJDate from a single inputted date, then you're left with requesting only three params: qDate, qSystem, and qExcept.
The string-to-date coercion in Access is actually not too bad.
qDOM = Int(Format(CDate([Enter the report date]), 'd'))
etc.
It's doable. Personally, I'd move this kind of logic out of the database entirely and into a middle tier. ;)
|
Post #151,831
4/19/04 4:55:09 PM
|
More
qDOM = Calendar day of month 1-31
qMonth = The month
qDOW = Alpha day of week (Sun ... Sat)
qJDate = Julian day
qAP = Day of the accounting period, based on a 28 day month. No correlation to actual calendar date.
Yes, I can generate the first three variable from a single date, and maybe the julian date, but I still need to pass to the query.
Other information that will be provided in number of days to run. That means that the query would be run x times to produce x schedules.
I've been doing it manually, but my co-workers are tech writers and I don't want to risk them missing a variable and not getting the schedules correct.
All for Disaster Recovery.
FWIW, I'm learning Access on the fly. All pointer are appreciated.
I'm trying Jay's VB suggestions and getting an error "Object Required" on the Set Query1 = line
|
Post #151,838
4/19/04 5:19:22 PM
|
Sorry
To use the example code I gave you need to do this first. It's one of those things that is so standard I skipped by it. I should also point out that I don't have a copy of Access handy, so there could be typos and other things I've overlooked.
Set db = CurrentDb()
Despite the fact that all Access code exists in the context of a database, Access does not give you a reference to the database by default. Thus the above is usually the first line of non-declare code in any significant Access VB routine.
I believe the reason for that has to do with the fact that you can create multiple database variable and access multiple databases through them. But Access really should give you access to the database the code is in through some default keyword.
Jay
|
Post #151,823
4/19/04 3:21:26 PM
|
Re: For *very* simple stuff
can even do the [enter parameter prompt] thing in QBE
A
Play I Some Music w/ Papa Andy
Saturday 8 PM - 11 PM ET
All Night Rewind 11 PM - 5 PM
Reggae, African and Caribbean Music
[link|http://wxxe.org|Tune In]
|
Post #151,829
4/19/04 4:38:56 PM
|
Or "query by form"
|
Post #151,943
4/20/04 2:24:10 PM
|
Found a way
Added another table to the query. This one is unlinked and contains all the necessary parameters. Set the parameter values on the form, then launch the query. Took a bit of experimentation to get the formatting right.
Now I just need figure out how to pass the name of the output table. I would like to have a new table for each of the days that the query is executed.
Thanks for all the assistance.
Joe
|
Post #152,627
4/26/04 12:48:43 PM
|
Different approach is DoCmd.RunSQL
You can dynamically generate the SQL string, which solves the issue of specifying the name of the output table. If you want you can use the QBE window to generate the SQL for you; just write the query the way you want it and look at the SQL view (same place as design view or data view).
FAQ! We're scrod!
|
Post #152,653
4/26/04 2:46:40 PM
|
How I've got it running
Query 1 does a make table. Get run first.
Query 2 does an append. Same query different parameters, gets run as many times as necessary.
Upon completion, auto export to an excel spreadsheet. Spreadsheet is named "Business Function" + DR TEST + "Start date of test". This spreadsheet is then sent to the Disaster Recovery Center for them to run. (Spreadsheet is schedule of jobs to run during the disaster recovery test).
Seems to work. We are running another test this week. Will see how it goes...
|
