473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Parameter Queries

NeoPa
32,556 Expert Mod 16PB
I would like to open a parameter query so that it's visible to the operator (not as a recordset in the VBA code) but I also want to set the parameters before it's opened so that I can control what the operator sees when it's run from a form, but so that it's also possible to run the query natively (double-click the querydef object from the database window) and have it ask for the parameters.

Is this possible?

PS. Please request clarification if anything is not clear.
Apr 2 '08 #1
24 2575
NeoPa
32,556 Expert Mod 16PB
An example of a parameter query (for illustration) would be :
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Branch] Long, [Start Time] DateTime;
  2. SELECT [Sales_Order_Nbr] AS OrderNo,
  3.        [TIME_PICKING_NOTE_PRINTED] AS Printed
  4. FROM Tran_PickPack
  5. WHERE [BRANCH_CODE]=Format([Branch],'00')
  6.   AND [DATE_PICKING_NOTE_PRINTED]=Date()
  7.   AND [TIME_PICKING_NOTE_PRINTED]>Format([Start Time],'Short Time')
  8. ORDER BY TIME_PICKING_NOTE_PRINTED,
  9.          [Sales_Order_Nbr]
Apr 2 '08 #2
MMcCarthy
14,534 Expert Mod 8TB
Try this ...


Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [EnterBranch:] As Branch Long, [Enter Start Time:] As StartTime DateTime;
  2. SELECT [Sales_Order_Nbr] AS OrderNo,
  3.        [TIME_PICKING_NOTE_PRINTED] AS Printed
  4. FROM Tran_PickPack
  5. WHERE [BRANCH_CODE]=Format([Branch],'00')
  6.   AND [DATE_PICKING_NOTE_PRINTED]=Date()
  7.   AND [TIME_PICKING_NOTE_PRINTED]>Format([StartTime],'Short Time')
  8. ORDER BY TIME_PICKING_NOTE_PRINTED,
  9.          [Sales_Order_Nbr]
Apr 2 '08 #3
NeoPa
32,556 Expert Mod 16PB
That's useful to know Mary (about renaming parameters), but I'm really after supplying the values in code that I already know, before then opening the PARAMETER SELECT query for full interaction with the operator.

I can set a PARAMETER value in code by using the :
Expand|Select|Wrap|Line Numbers
  1. qdf(ParmName) = Value
...syntax, but that only allows programmatic access to the resultant data. I want it in a query grid as the operator is used to using when opening the query themselves.

PS. This may be entirely unsupported in Access. If so then I must look at alternative approaches of course.
Apr 2 '08 #4
MMcCarthy
14,534 Expert Mod 8TB
Where are you getting the values from?
Apr 2 '08 #5
NeoPa
32,556 Expert Mod 16PB
I'm not yet, but I was planning to set up an unbound form for them.

I didn't go there as I was trying to avoid any comments along the lines of "Put a reference to the form controls in place of the parameters." which would tie the query down to working only from the form.
Apr 2 '08 #6
NeoPa
32,556 Expert Mod 16PB
I'm particularly interested in the concept of doing this as, once done, I will have more scope to design simpler queries rather than always having to turn results out as reports. Thereby reducing development times.

I appreciate this idea would not work in all environments, but my office is somewhere I can provide some basic queries for my IT colleagues to run competently where necessary.

The more they can handle independently, the less involved I need to be with executing noddy queries.
Apr 2 '08 #7
MMcCarthy
14,534 Expert Mod 8TB
I'm not yet, but I was planning to set up an unbound form for them.

I didn't go there as I was trying to avoid any comments along the lines of "Put a reference to the form controls in place of the parameters." which would tie the query down to working only from the form.
Not sure if I'm fully following what you are trying to do but you can use "User Defined" funtions in a query. Therefore you could design a function to return for instance the Long value of branch depending on whatever criteria you set up and call the function as follows:

Parametre getBranch() As Branch Long

I have never tried to use a function in this way so I suggest you run a simple test function to try it out. You can use them in the criteria if you can't use them in the Parametres.

Mary
Apr 2 '08 #8
Denburt
1,356 Expert 1GB
I'm really after supplying the values in code that I already know, before then opening the PARAMETER SELECT query for full interaction with the operator.
Couldn't you just build the SQL in code as they fill in the forms info then they could click to see the query in it's design state then when they close the form it replaces the actual values with the parameters?
Apr 2 '08 #9
NeoPa
32,556 Expert Mod 16PB
I'm trying to make it easier and less complicated Denburt. I don't want users ever to have to redesign the query when using it (or even be able to really). Thanks for the thoughts though.
Apr 2 '08 #10
MMcCarthy
14,534 Expert Mod 8TB
OK Try this ...

Using this SQL
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [EnterBranch:] As Branch Long, [Enter Start Time:] As StartTime DateTime;
  2. SELECT [Sales_Order_Nbr] AS OrderNo,
  3.        [TIME_PICKING_NOTE_PRINTED] AS Printed
  4. FROM Tran_PickPack
  5. WHERE [BRANCH_CODE]=Format([Branch],'00')
  6.   AND [DATE_PICKING_NOTE_PRINTED]=Date()
  7.   AND [TIME_PICKING_NOTE_PRINTED]>Format([StartTime],'Short Time')
  8. ORDER BY TIME_PICKING_NOTE_PRINTED,
  9.          [Sales_Order_Nbr]
with this code
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As QueryDef
  2.  
  3. Set qdf = CurrentDB.QueryDefs("Your Query Name")
  4. qdf.Parameters("Branch") = 3
  5. qdf.Parameters("StartTime") = Now()
  6. qdf.Execute "Your Query Name"
  7.  
Apr 3 '08 #11
NeoPa
32,556 Expert Mod 16PB
Everything works fine until the qdf.Execute statement.
Execute Method

Runs an action query or executes an SQL statement on a specified Connection or Database object.
But...
Remarks

The Execute method is valid only for action queries. If you use Execute with another type of query, an error occurs.
Apr 3 '08 #12
MMcCarthy
14,534 Expert Mod 8TB
Everything works fine until the qdf.Execute statement.

But...
I'll do some more research :)

However, what happens if you try to open the query as normal instead of executing it?
Apr 3 '08 #13
MMcCarthy
14,534 Expert Mod 8TB
I'll do some more research :)

However, what happens if you try to open the query as normal instead of executing it?
OK based on further research it looks like you can't pass parameters in this manner except to an action query. For a select query it seems you can only return a recordset.

replace qdf.Execute "Query Name" with qdf.OpenRecordset("QueryName")

You could then do a workaround and open a datasheet form based on the recordset rather than the query.
Apr 3 '08 #14
ADezii
8,834 Expert 8TB
I would like to open a parameter query so that it's visible to the operator (not as a recordset in the VBA code) but I also want to set the parameters before it's opened so that I can control what the operator sees when it's run from a form, but so that it's also possible to run the query natively (double-click the querydef object from the database window) and have it ask for the parameters.

Is this possible?

PS. Please request clarification if anything is not clear.
Comes with no guarantee whatsoever:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Branch] Long, [Start Time] DateTime;
  2. SELECT [Sales_Order_Nbr] AS OrderNo,
  3. [Branch] As Param1, [Start Time] As Param2,
  4. [TIME_PICKING_NOTE_PRINTED] AS Printed
  5. FROM Tran_PickPack
  6. WHERE [BRANCH_CODE]=Format([Branch],'00')
  7. AND [DATE_PICKING_NOTE_PRINTED]=Date()
  8. AND [TIME_PICKING_NOTE_PRINTED]>Format([Start Time],'Short Time')
  9. ORDER BY TIME_PICKING_NOTE_PRINTED,
  10. [Sales_Order_Nbr]
  11.  
Apr 3 '08 #15
NeoPa
32,556 Expert Mod 16PB
Thanks to all that provided suggestions to check out.

All suggestions have been looked at, but after that, and some further extensive research (Mary helped here bless her), I'm convinced that this facility is not supported by any version of MS Access yet :(
Apr 3 '08 #16
Scott Price
1,384 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Branches Text ( 255 );
  2. SELECT Table1.Branch
  3. FROM Table1
  4. WHERE Table1.Branch=IIf(IsNull(Forms!Form1.Text0.Text),[Branches],Forms!Form1.Text0.Text);
  5.  
As I see it, the main problem is being able to use one criteria OR the other criteria as your parameter. If the form is open, the query needs to know to use it and not ask for the other, however, if the form is closed it needs to know to use the parameter... The above sql statement gets close, but not quite ;-( It has the criteria back to front... if the parameter isn't given it looks for the form rather than looking first for the form then later the parameter input box.

I think you might be out of luck on this one (unless A2007 supports it, horrors :-)

Take care,
Scott
Apr 3 '08 #17
ADezii
8,834 Expert 8TB
I would like to open a parameter query so that it's visible to the operator (not as a recordset in the VBA code) but I also want to set the parameters before it's opened so that I can control what the operator sees when it's run from a form, but so that it's also possible to run the query natively (double-click the querydef object from the database window) and have it ask for the parameters.

Is this possible?

PS. Please request clarification if anything is not clear.
Hello NeoPa, I think I may have found an 'innovative' but rather 'bizarre' solution to your dilemma. For argument sake, let's assume your original Select Query is named qryParameters:
  1. Copy and Paste qryParameters, name it qryParameters2, make qryParameters2 a Make Table Query in the QBE Grid, and enter tblTemp for the New Table Name:
  2. Execute the following code. I ran it from the Click() Event of a Command Button on an Unbound Form.
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef, prm As DAO.Parameter
    2. Dim tdf As DAO.TableDef
    3.  
    4. 'Assume 2 Text Boxes on an Unbound Form named txtBranch and txtStartTime.
    5. 'These Text Boxes will contain the User Entered Values for the Branch and
    6. 'Start Time Parameters in qryParameters2. Let's make sure that they are
    7. 'both not Null (contain data, validity not checked)
    8. If IsNull(Me![cboRank]) Or IsNull(Me![cboVacationCode]) Then Exit Sub
    9.  
    10. 'Delete tblTemp if it should pre-exist
    11. For Each tdf In CurrentDb.TableDefs
    12.   If tdf.Name = "tblTemp" Then CurrentDb.TableDefs.Delete tdf.Name
    13. Next
    14.  
    15. Set qdf = CurrentDb.QueryDefs("qryParameters2")
    16.  
    17. 'Retrieve the Parameters from the Text Boxes, and place them into the
    18. 'Parameters Collection of qryParameters2
    19. qdf.Parameters("Branch") = Me![txtBranch]
    20. qdf.Parameters("Start Time") = Me![txtStartTime]
    21.  
    22. 'Execute the darn thing and create tblTemp
    23. qdf.Execute
    24.  
    25. 'Let's see the fruits of our labor and open tblTemp. This entire process
    26. 'is almost completely transparent to the User except for the Title Bar of
    27. 'tblTemp. A Query based on tblTemp could also be opened.
    28. DoCmd.OpenTable "tblTemp"
  3. Both of your conditions now seem to be satisfied since qryParameters can be executed via a traditional dbl-click in the Queries Tab, a User can now manually enter Parameter Values in Text Boxes on an Unbound Form where they will be evaluated within the context of a Make Table Query qryParameters2.
  4. Almost the entire process is transparent to the User.
  5. It should not be that difficult to dynamically create the SQL to make an exact copy of the original, Select Query (qryParameters), but convert it to a Make Table Query (qryParameters2). This is in the event you don't like the manual part.
  6. I'm just dying to hear what you have to say on this one, probably Get The White Jacket for ADezii (LOL).
Apr 4 '08 #18
NeoPa
32,556 Expert Mod 16PB
Here, let me just help you with those buckles my friend :)
I know they can be awkward right around the back there.

I do appreciate the effort you've gone to, of course. My problem with this suggestion is that I'm really looking for a simple (or not simple but certainly straightforward) way to use a PARAMETERS query from two perspectives. I am avoiding complicating the matter as I hope to use the technique over and over again as a mainstay of my databases, and possibly something I could recommend to members on here in similar circumstances.

I'm afraid that creating a temp table would not really suit this. I could also create a temp query with the parameters embedded in it, but I'd have to use a different name, and manage deleting it afterwards, which rather takes us out of straightforward territory. Sorry ADezii.
Apr 4 '08 #19
fascinating stuff. lol NeoPa :)
how can I subscribe to a thread w/o posting? doh!
Apr 4 '08 #20
ADezii
8,834 Expert 8TB
Here, let me just help you with those buckles my friend :)
I know they can be awkward right around the back there.

I do appreciate the effort you've gone to, of course. My problem with this suggestion is that I'm really looking for a simple (or not simple but certainly straightforward) way to use a PARAMETERS query from two perspectives. I am avoiding complicating the matter as I hope to use the technique over and over again as a mainstay of my databases, and possibly something I could recommend to members on here in similar circumstances.

I'm afraid that creating a temp table would not really suit this. I could also create a temp query with the parameters embedded in it, but I'd have to use a different name, and manage deleting it afterwards, which rather takes us out of straightforward territory. Sorry ADezii.
Not a problem, NeoPa. Can you take the buckles off now! (LOL).
Apr 4 '08 #21
NeoPa
32,556 Expert Mod 16PB
fascinating stuff. lol NeoPa :)
how can I subscribe to a thread w/o posting? doh!
Currently there's not - but you can delete an added post which will leave you subscribed if you prefer.

It's an issue that is still being looked at and I hope that it will be returned to us soon.

@ADezii
Sure I can my friend. Can I just suggest you stop struggling so violently for a short time, to give me better access to the straps :D
Apr 4 '08 #22
FishVal
2,653 Expert 2GB
Actually, I don't see so far any "straightforward" method using native Access mechanism for passing parameters into query via popup dialog.
IMHO, it will be more controllable and reliable to mimick this behavior.

Example:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetParam(strExpression As String, _
  2.                          strPropmpt As String) As Variant
  3.  
  4.     On Error GoTo FormRefFailed
  5.  
  6.     Set GetParam = Eval(strExpression)
  7.     Exit Function
  8.  
  9. FormRefFailed:
  10.     'for a better appearance custom form may be used as a dialog
  11.     GetParam = InputBox(strPropmpt, "Enter Parameter Value")
  12.     If GetParam = "" Then End
  13.  
  14. End Function
  15.  
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl.*
  2. FROM tbl
  3. WHERE tbl.txt=GetParam("Forms!frm!txbTxtFldFilter","Enter filter value for [txt] field:");
  4.  
Apr 4 '08 #23
NeoPa
32,556 Expert Mod 16PB
Indeed not Fish.

That's the conclusion I'd come to in post #16.
Apr 4 '08 #24
NeoPa
32,556 Expert Mod 16PB
OK Try this ...

Using this SQL
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [EnterBranch:] As Branch Long, [Enter Start Time:] As StartTime DateTime;
  2. SELECT [Sales_Order_Nbr] AS OrderNo,
  3.        [TIME_PICKING_NOTE_PRINTED] AS Printed
  4. FROM Tran_PickPack
  5. WHERE [BRANCH_CODE]=Format([Branch],'00')
  6.   AND [DATE_PICKING_NOTE_PRINTED]=Date()
  7.   AND [TIME_PICKING_NOTE_PRINTED]>Format([StartTime],'Short Time')
  8. ORDER BY TIME_PICKING_NOTE_PRINTED,
  9.          [Sales_Order_Nbr]
...
I was actually very interested in taking this further as I felt it had scope. Unfortunately, my tests showed that the Help on the subject hadn't left anything out :(
Syntax
PARAMETERS name datatype [, name datatype [, ...]]
No Aliases allowed in the PARAMETERS section.
Apr 11 '08 #25

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Lapchien | last post by:
I have 2 queries, 1 produces a list of direct debit payments, the other then 'marks' those records as processed. The parameter is a date (usually today's date). I run these 2 queries from a...
2
by: Simon Pleasants | last post by:
Am something of a newbie at this, so please bear with any stupid questions. I have created a database to track shipments that we import. The information is stored in a table and I have created...
1
by: Cory | last post by:
When I run the subroutine testEmailContacts the msgbox says that there is only 1 record. The sql for qyEmailContactsQyCard is below. There is over 3000 records in the table "tbl:Contact". What am i...
21
by: Marc DVer | last post by:
I am trying to create a query that can be loaded as a querydef object but not having to assign values to the parameters if I don't want to. Normally when using a parameter query in VBA my code...
0
by: tlyczko | last post by:
Can anyone give me a website reference(s) wherein I can learn how to use a form and parameter queries to generate an Access report *without* having to reference the form or hard-code the query in...
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
0
by: Billie Boy | last post by:
Hi to all. I’m new here and am coming to you from Melbourne Australia. So a big HELLO 2 ALL. Now I am encountering an annoying problem in the SQL builder of the copy of VB.6 that I am using at...
1
by: TonyJH | last post by:
Hello, I have a database in Access2003. I have set up several queries that use the parameter entry . Each query produces different outputs from various tables that go into a report. Sometimes, a...
4
by: David Wright | last post by:
Hello Folks I have a Macro which consecutively opens seven ‘Make Table Query’s’. Each query requests two parameter inputs i.e. ‘Please Enter Department Code’ and ‘Please Enter Year’. It is...
8
by: DanicaDear | last post by:
I have something interesting...looking to see if anyone else has came across this. I have a query with parameter and and the query works beautifully every time. However, when I use the wizard...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.