469,286 Members | 2,521 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

How to put a "drop-down" list for a query parameter?

sueb
379 256MB
I want to create a query for my Surgery database that asks for a Start Date and an End Date (easy), but also asks for a Surgeon using a drop-down list. This list already exists (the main database already uses this list to accept surgeon's names in its records), but I don't know how to let the user choose from that list in the parameters to the query.

Here's the query's SQL so far:

Expand|Select|Wrap|Line Numbers
  1. SELECT DataTable.[Date of Surgery], DataTable.Surgeon, DataTable.Operation
  2. FROM DataTable
  3. WHERE (((DataTable.[Date of Surgery]) Between [Start Date] And [End Date]) AND ((DataTable.Surgeon)=[Enter Surgeon]));
  4.  
Mar 22 '11 #1

✓ answered by ADezii

You could create a Criteria Form, then allow the User to enter a [Start Date], [End Date], and select a [Surgeon] from a Combo Box. Once the three Criteria have been Validated, you can then modify the SQL Property of a Base Query consisting of no Criteria. Then, simply Open the Query. There is no way, that I am aware of, to display a List of Options for Criteria in a Parameterized Query.

24 3853
ADezii
8,800 Expert 8TB
You could create a Criteria Form, then allow the User to enter a [Start Date], [End Date], and select a [Surgeon] from a Combo Box. Once the three Criteria have been Validated, you can then modify the SQL Property of a Base Query consisting of no Criteria. Then, simply Open the Query. There is no way, that I am aware of, to display a List of Options for Criteria in a Parameterized Query.
Mar 22 '11 #2
sueb
379 256MB
Ah, I see. Okay, I'll do that, but I've never "modfied the SQL Property" of a query. How is that property accessed? And do I, in my form's code, make a string to put in that property? Or what?
Mar 22 '11 #3
ADezii
8,800 Expert 8TB
  1. Let's assume you created a Query (qryEmployees) to return ALL Records in tblEmployees, namely:
    Expand|Select|Wrap|Line Numbers
    1. SELECT * FROM tblEmployees;
  2. To now Modify, and then Run the Query, so that only Employees who live in the State of PA with a Zip Code of 19145, you can dynamically change the SQL Property of the Query then Execute it:
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
    2.  
    3. Set qdf = CurrentDb.QueryDefs("qryEmployees")
    4.  
    5. qdf.SQL = "SELECT * FROM tblEmployees WHERE [State] = 'PA' AND [Zip] = 19145"
    6.  
    7. DoCmd.OpenQuery "qryEmployees", acViewNormal, acReadOnly
P.S. - Be sure to Delimit your Date Fields ([Start] and [End]) by (#'s), and your String Field ([Surgeon]) by "'".
Mar 22 '11 #4
sueb
379 256MB
If I actually want to run a report based on that query, how does line 7 change?
Mar 22 '11 #5
sueb
379 256MB
So now I have the form, with 3 boxes (Surgeon_Choice, Start_Date_Choice, and End_Date_Choice), and a button that triggers the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Create_the_Report_Click()
  2. On Error GoTo Err_Create_the_Report_Click
  3.  
  4.     Dim qdf As DAO.QueryDef
  5.  
  6.     Set qdf = [VCMC Surgery].QueryDefs("Surgeon's Monthly Count")
  7.  
  8.     qdf.SQL = "SELECT DataTable.[Date of Surgery], DataTable.Surgeon, DataTable.Operation " & _
  9.               "FROM DataTable WHERE [Date of Surgery] between Me.Start_Date_Choice and Me.End_Date_Choice " & _
  10.               "and [Surgeon] = Me.Surgeon_Choice"
  11.  
  12.     DoCmd.OpenQuery "Surgeon's Monthly Count", acViewNormal, acReadOnly
  13.  
  14. Exit_Create_the_Report_Click:
  15.     Exit Sub
  16.  
  17. Err_Create_the_Report_Click:
  18.     MsgBox Err.Description
  19.     Resume Exit_Create_the_Report_Click
  20.  
  21. End Sub
  22.  
I'm getting the error "VCMC Surgery can't find the field '|' referred to in your expression." VCMC Surgery is the name of my database, but I don't know where the '|' is coming from.
Mar 22 '11 #6
NeoPa
32,173 Expert Mod 16PB
You can also design a query, that needn't change, which refers to the values of the actual objects on your form. The WHERE clause would look a bit like :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([Date of Surgery] Between Forms("ParameterForm").txtStartDate And Forms("ParameterForm").txtEndDate)
  2.   AND ([Surgeon] = Forms("ParameterForm").cboSurgeon)
Mar 23 '11 #7
NeoPa
32,173 Expert Mod 16PB
BTW your SQL code at line #8 should look more like the following if you want it to work in the way I expect you do :
Expand|Select|Wrap|Line Numbers
  1. qdf.SQL = "SELECT DataTable.[Date of Surgery], DataTable.Surgeon, DataTable.Operation " & _
  2.           "FROM DataTable WHERE ([Date of Surgery] between #" & _
  3.           Format(Me.Start_Date_Choice, "m/d/yyyy") & "#) and (#" & _
  4.           Format(Me.End_Date_Choice, "m/d/yyyy") & _
  5.           "#) and ([Surgeon] = " & Me.Surgeon_Choice & ")"
This changes for the Surgeon if it's a string value to :
Expand|Select|Wrap|Line Numbers
  1.           "#) and ([Surgeon] = '" & Me.Surgeon_Choice & "')"
See Quotes (') and Double-Quotes (") - Where and When to use them.
Mar 23 '11 #8
sueb
379 256MB
NeoPa, I really like the idea of the query referencing the parameter form, because it seems like then the report-running button on the form would work more straightforwardly. So I modified my query some, but am getting the following error when I switch from SQL view to design view:

Invalid use of '.', '!', or '()', in criteria expression

It looks to me like my parentheses are matched up correctly; what do you think?

Expand|Select|Wrap|Line Numbers
  1. SELECT DataTable.[Date of Surgery], DataTable.Surgeon, DataTable.Operation
  2. FROM DataTable 
  3. WHERE ([Date of Surgery] 
  4.    Between Forms("Parameters: Surgeon's Monthly Report").Start_Date_Choice 
  5.        And Forms("Parameters: Surgeon's Monthly Report").End_Date_Choice) 
  6.    AND ([Surgeon] = Forms("Parameters: Surgeon's Monthly Report").Surgeon_Choice);
  7.  
Mar 23 '11 #9
NeoPa
32,173 Expert Mod 16PB
I'm sorry. I fed you some misinformation there. That is one of a number of valid forms for using in VBA but not one that SQL supports. Try instead :

Expand|Select|Wrap|Line Numbers
  1. SELECT [Date of Surgery]
  2.      , [Surgeon]
  3.      , [Operation]
  4. FROM   [DataTable]
  5. WHERE ([Date of Surgery] Between
  6.        Forms![Parameters: Surgeon's Monthly Report]!Start_Date_Choice
  7.        And
  8.        Forms![Parameters: Surgeon's Monthly Report]!End_Date_Choice)
  9.   AND ([Surgeon] = Forms![Parameters: Surgeon's Monthly Report]!Surgeon_Choice);
Mar 23 '11 #10
sueb
379 256MB
Ah, great! So now my query thinks it's happy, but when I click the "run report" button on my form (after choosing surgeon and entering dates), it complains about the syntax in my date formatting. Here's the button's code (followed by the query's SQL):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Run_the_Report_Click()
  2. On Error GoTo Err_Run_the_Report_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "Surgeon's Monthly Count"
  7.     DoCmd.OpenReport stDocName, acPreview
  8.  
  9. Exit_Run_the_Report_Click:
  10.     Exit Sub
  11.  
  12. Err_Run_the_Report_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_Run_the_Report_Click
  15.  
  16. End Sub
  17.  
Expand|Select|Wrap|Line Numbers
  1. SELECT DataTable.[Date of Surgery]
  2.       , DataTable.Surgeon
  3.       , DataTable.Operation
  4.       , DataTable.Surgeon 
  5.            AS ForSurgeon
  6.       , Forms![Parameters: Surgeon's Monthly Report]![Start Date Choice] 
  7.            AS StartDate
  8.       , Forms![Parameters: Surgeon's Monthly Report]![End Date Choice] 
  9.            AS EndDate
  10. FROM DataTable
  11. WHERE (
  12.        ((DataTable.[Date of Surgery]) Between 
  13.            #Format([Forms]![Parameters: Surgeon's Monthly Report]![Start Date Choice], "m/d/yyyy")# 
  14.            And 
  15.            #Format([Forms]![Parameters: Surgeon's Monthly Report]![End Date Choice], "m/d/yyyy")#
  16.        ) 
  17.    AND ((DataTable.Surgeon)=[Forms]![Parameters: Surgeon's Monthly Report]![Surgeon Choice]
  18.        )
  19.       );
  20.  
I'm hoping I just have the #'s in the wrong place or something.
Mar 23 '11 #11
NeoPa
32,173 Expert Mod 16PB
I think you may have jumped to an unsupported conclusion here Sue. The date values from the form are not literals. They don't need hash (#) characters around them (I'm not sure where that idea came from as it's certainly not in my suggested code).

The only thing I can think may be an issue, assuming my suggestion doesn't work (which I can't tell ATM as you've tried something quite different), is that the date values entered on the form are not recognised as such. There is no way, after all, of specifying that the TextBox control be treated as containing a DateTime type value. We can handle that if necessary of course, but only when we know exactly what we're dealing with.
Mar 23 '11 #12
sueb
379 256MB
Okay, I removed that whole "#Format..., 'mm/dd/yy'#" stuff (I think it got it from post #8), and I'm still being asked for all the parameters. After I do enter them, the report runs correctly.

So it looks like mainly I'm not successfully passing the user's entries in to the query/report.
Mar 23 '11 #13
ADezii
8,800 Expert 8TB
@sueb - Based on what NeoPa has said in Post #12, you may wish to provide some form of Validation prior to Opening the Report, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2.  
  3. 'The Start and End Fields must be Valid Dates, and the Surgeon Field cannot be NULL
  4. If Not IsDate(Me![Start Date Choice]) Or Not IsDate(Me![End Date Choice]) Or _
  5.        IsNull(Me![Surgeon Choice]) Then
  6.   MsgBox "One or more Fields have either incorrect Values, or missing Values", _
  7.           vbCritical, "Missing/Incorrect Values"
  8.     Exit Sub
  9. End If
  10.  
  11. stDocName = "Surgeon's Monthly Count"
  12.  
  13. DoCmd.OpenReport stDocName, acPreview
Mar 23 '11 #14
sueb
379 256MB
Good idea, ADezii--I'll add that to the button's code.
Mar 23 '11 #15
NeoPa
32,173 Expert Mod 16PB
Post #7 was a suggestion I felt you'd be happiest with.
Post #8 was an alternative (completely different) approach that can work under some circumstances.
Post #10 was a clarification/erratum fix for the method used in post #7.

You say those extraneous bits have now been removed and it still doesn't work. I'd want to know two things :
  1. Is the form open when the report is run?
  2. What is the actual SQL of the query being run?

It's easy to make assumptions, and it's easy to make changes and think you have it right, but without seeing the actual code used it's hard to know exactly. I've tried the concept here on a test database and it works. It doesn't need validation to work. That said, validation is a very good idea in its own right and should be included, for running by others particularly.
Mar 23 '11 #16
sueb
379 256MB
Yes, the form is open. The button is on the form, and you have to close the form manually (it doesn't close just by clicking the "run the report" button).

Here's the query code:

Expand|Select|Wrap|Line Numbers
  1. SELECT DataTable.[Date of Surgery]
  2.        , DataTable.Surgeon
  3.        , DataTable.Operation
  4.        , DataTable.Surgeon 
  5.             AS ForSurgeon
  6.        , Forms![Parameters: Surgeon's Monthly Report]![Start Date Choice] 
  7.             AS StartDate
  8.        , Forms![Parameters: Surgeon's Monthly Report]![End Date Choice] 
  9.             AS EndDate
  10. FROM DataTable
  11. WHERE (((DataTable.[Date of Surgery]) Between 
  12.             Forms![Parameters: Surgeon's Monthly Report]![Start Date Choice] 
  13.             And 
  14.             Forms![Parameters: Surgeon's Monthly Report]![End Date Choice]) 
  15.          And 
  16.         ((DataTable.Surgeon)=Forms![Parameters: Surgeon's Monthly Report]![Surgeon Choice]));
  17.  
Mar 23 '11 #17
NeoPa
32,173 Expert Mod 16PB
Being prompted for the parameters should only happen if the parameter references entered are not correct. The form of the references is perfectly correct, so I can only imagine that somewhere the names don't match. When were the underscores lost from [Start Date Choice] BTW? They were there in post #9 but gone by post #11. That could explain it possibly. Fundamentally though, you should be looking for somewhere that a name isn't exactly correct.
Mar 23 '11 #18
sueb
379 256MB
gaaah! Why do I have such trouble with these names?? The concept seems clear enough to me, but I find myself tripping over them all the time!

*pant* *pant*

Okay, here's what I have (I went through all the names and removed as many non-alphabetic characters as I could without actually changing table field names, so things will look a little different):

My form, Parameters_Surgeon's_Monthly_Choice, has three controls:

SurgeonChoice, an unbound combo box with the following Row Source (this works as expected, offering the correct set of values, and objecting when an incorrect one is entered):
Expand|Select|Wrap|Line Numbers
  1. SELECT [Surgeons].[Surgeon] FROM [Surgeons] ORDER BY [Surgeon];

StartDateChoice, an unbound text box with no source or validation of any kind

EndDateChoice, another unbound text box with no source or validation

A report button, RunTheReport, that triggers the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub RunTheReport_Click()
  2. On Error GoTo Err_RunTheReport_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "Surgeon's Monthly Count"
  7.     DoCmd.OpenReport stDocName, acPreview
  8.  
  9. Exit_RunTheReport_Click:
  10.     Exit Sub
  11.  
  12. Err_RunTheReport_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_RunTheReport_Click
  15.  
  16. End Sub
  17.  

My query, Surgeon's_Monthly_Count, has the following SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT DataTable.[Date of Surgery]
  2.        , DataTable.Surgeon
  3.        , DataTable.Operation
  4.        , DataTable.Surgeon 
  5.             AS ForSurgeon
  6.        , Forms![Parameters_Surgeon's_Monthly_Report]!StartDateChoice 
  7.             AS StartDate
  8.        , Forms![Parameters_Surgeon's_Monthly_Report]!EndDateChoice 
  9.             AS EndDate
  10. FROM DataTable
  11. WHERE (
  12.        ((DataTable.[Date of Surgery]) 
  13.           Between 
  14.           Forms![Parameters_Surgeon's_Monthly_Report]!StartDateChoice 
  15.           And 
  16.           Forms![Parameters_Surgeon's_Monthly_Report]!EndDateChoice) 
  17.        And ((DataTable.Surgeon)=Forms![Parameters_Surgeon's_Monthly_Report]!SurgeonChoice));
  18.  
The report I'm not going to bother putting up here, because, once I manually supply the SurgeonChoice and the two dates, it gets executed and runs perfectly.

What am I not seeing?
Mar 23 '11 #19
NeoPa
32,173 Expert Mod 16PB
If your form is called Parameters_Surgeon's_Monthly_Choice, then why are the references to Forms![Parameters_Surgeon's_Monthly_Report]!...?
Mar 24 '11 #20
sueb
379 256MB
@NeoPa, I really laughed out loud at myself when I read your post! I knew it had to be something like that, and I just couldn't see it.

Thanks for the sharp eye--now it works perfectly.
Mar 24 '11 #21
sueb
379 256MB
I chose ADezii's answer for its concept, even though I used NeoPa's suggestion of a query that didn't need SQL modification. I wish there were a way to indicate that.

Would it be permissible for me to edit my original post to indicate the two post numbers?
Mar 24 '11 #22
NeoPa
32,173 Expert Mod 16PB
Don't worry about it Sue. It's an imperfect system (Marking just one post as Best Answer), but it's still the best we have. It's a good system too, in that it covers most threads adequately and tweaking it may plug some gaps, but probably introduce others. Many threads don't even get a post flagged. No-one can afford to worry about it too much (and we generally don't), but your attention to being as fair as you can be does you credit nevertheless.

I think I can speak for ADezii and many others when I say that, win or lose one or two, it's hardly even noticed. As long as we see that the member asking the question (The OP) gets a reliable answer to their question. I think we can both be happy with that in this case :-)

PS. I expect editing your original post would be acceptable yet unnecessary. We don't encourage retrospective edits which change the purport of historical posts, but what you suggest wouldn't have that effect so would be fine.
Mar 25 '11 #23
sueb
379 256MB
NeoPa, I realize that the effort put into this site is "for the love" (and that shows!), but several times (and this thread is one), the fullest answer is provided from more than one source. I guess that I'm really thinking of the poor souls who come after, wishing to point them to the "best of the best".

Of course, they always have the option of reading the entire thread (which is what I usually do), so you're probably right: simplest probably is best.
Mar 25 '11 #24
ADezii
8,800 Expert 8TB
@sueb: Concerning NeoPa's Quote
I think I can speak for ADezii and many others when I say that, win or lose one or two, it's hardly even noticed. As long as we see that the member asking the question (The OP) gets a reliable answer to their question. I think we can both be happy with that in this case :-)
NeoPa can always speak for me, and I do share his exact same sentiments. We do appreciate your concern and honesty in this matter.
Mar 25 '11 #25

Post your reply

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

Similar topics

23 posts views Thread by ian justice | last post: by
5 posts views Thread by Paul Miller | last post: by
6 posts views Thread by Otto Wyss | last post: by
2 posts views Thread by Alanoly J. Andrews | last post: by
reply views Thread by Felix Finch | last post: by
1 post views Thread by blee via AccessMonster.com | last post: by
9 posts views Thread by Keith G Hicks | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.