|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Parameter Query By Richard Rost 
4 years ago
Use Parameters to Ask for User Input in Query In today's video, I'm going to show you how to prompt the user for some data, called a parameter, when running a query in Microsoft Access. We'll talk about standard criteria, inequalities, the BETWEEN keyword, LIKE and wildcards, getting a value from an open form, and more. Links
Recommended Course
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, creating a parameter query in access, Query Parameters, inequalities, criteira, Use parameters to ask for input when running a query, between, wildcards, like, Create a parameter query, Combine parameters with wildcards, Use parameters in queries forms and reports, What is parameter query in Access, How do you create a parameter query in Access, How do you enter parameter values in Access, access run query with parameters from form, Query Parameters Dialog
IntroIn this video, I will show you how to create and use parameter queries in Microsoft Access to prompt users for input when running a query. We will cover how to set up query prompts using square brackets, work with criteria for exact matches, use inequalities and date ranges, and perform wildcard searches with the LIKE keyword. I will also explain common mistakes that can cause unexpected parameter prompts and briefly mention how to specify parameter data types.TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to use parameters to ask a user for input when running a query in Microsoft Access.If you do not know what query criteria are, go watch this video first. I will put a link down below in the link section. Click on that and watch it, then come back. We can use standard query criteria to see a certain bit of information when we run our queries. For example, if we only want to see customers from New York, we create a query and, in the criteria row, we put "New York" inside of quotes in the State column. Then we will see just the records from New York when we run the query. This is all fine and dandy, but if you want to make changes, such as seeing people from Florida or Pennsylvania, then you either have to go into design view and change it, or you have to make 50 different queries. Neither one of those is a very good solution. That is where a parameter query comes in. With a parameter query, we put the parameter inside of square brackets. See that? I just put the words "Enter the state." That is my prompt. When I run the query now, it will say "Enter Parameter Value: Enter the state." The user types in whatever value they want, such as New York, Pennsylvania, Texas, or whatever, and then they will see the records they have selected. Here is my query. I have "Enter the state" as the criteria, a parameter criteria for State. I run it and now type in the state, Florida. Press Enter, and there are my Floridians. Want to run it again? Run it and type in New York. There are the New Yorkers. See how easy that is? The user can run that without having to make any design changes to your database. Just save this as My Customer Query, and then you can make a button to open it, or you can come over here and run it like this and it prompts them. You type in Texas, and I got nobody from Texas. But you see how easy that is. Now, this type of query gives you an exact match, but you can also use inequalities. For example, put a greater than sign here and then say "Greater than start date" as your criteria. When you run this query, it will give you all the orders that are greater than that start date. You can use the BETWEEN keyword and say "Between start date and end date." That will give you a range of date values. Be careful, though, because if you have times in your date values for your orders, you have to be sure to use "greater than or equal to start date" and "less than end date," because the BETWEEN keyword will not work. I have a whole separate video on that. Here is a link right there. You will find it down below in the link section. Go watch that if you have times in your date values. You can also use wildcard searches and the LIKE keyword, like "LIKE *" and then your last name search and then another star. You can type in any part of the user's name, and it will find it for you. Again, I have a whole separate video using the LIKE keyword and wildcard searches. If you do not want to get the parameter from the query itself, you can create a form with a field on it like this, click a button, and the query will get that parameter from the form. Again, whole separate video on that. Watch my form name video. These are all free videos, by the way. They are on my website, they are on my YouTube channel, and they are really good. Now, what happens if you get the "Enter Parameter Value" box and you are not expecting to? What if you just see this all of a sudden when you run your query and you have no idea what it is? Chances are, nine times out of ten, you have something spelled wrong. Again, I have a separate video on this "Enter Parameter Value" issue. Go watch that. Finally, a little more advanced. I covered this in my Access Beginner Level 9 class. There is actually a Parameters dialog box you can open, where you can specify each individual parameter and give it a data type. For example, a start date has to be a date. You can specify that value and it will warn the user if they do not type in a proper date value. Again, that is in my Access Beginner 9 class. There you go. There is the quick and dirty on how to use parameter queries. There are lots more videos about this stuff on my website, AccessLearningZone.com. Hope you learned something and we will see you next time. QuizQ1. What is the primary benefit of using a parameter query in Microsoft Access?A. It lets users enter their own criteria when running a query B. It automatically creates new queries for each search C. It forces users to enter all record data D. It filters data only for numeric values Q2. How do you create a parameter prompt in a query? A. Use curly braces with your prompt {Enter the state} B. Put your prompt inside square brackets [Enter the state] C. Use double quotes "Enter the state" D. Enter your prompt in the query title Q3. What happens if you type "Florida" when prompted by the parameter [Enter the state]? A. The query will display all records except Florida B. The query will show only records from Florida C. The query will delete records from Florida D. The query will show records from all states Q4. What should you do if you see an "Enter Parameter Value" box unexpectedly? A. Ignore it and continue B. Check for spelling mistakes in your query C. Restart Access D. Reinstall your database Q5. How can you search for a partial match using a parameter query? A. Use the SUM keyword B. Use BETWEEN in the criteria C. Use the LIKE keyword with wildcards D. Use ORDER BY Q6. Why should you avoid using BETWEEN for dates that include times? A. BETWEEN only works with numbers B. BETWEEN ignores times in date values C. BETWEEN will not match values with times correctly D. BETWEEN only works for text fields Q7. What is an advanced feature of parameter queries mentioned in the video? A. Using a macro to automate queries B. Specifying parameter data types in the Parameters dialog box C. Saving the parameter prompt to a text file D. Converting queries to macros Q8. If you want to get a parameter value from a form instead of the prompt, what should you do? A. Delete the query criteria B. Add a field to a form and link the query to it C. Export the query to Excel D. Use only tables Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-C; 7-B; 8-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on how to use parameters to prompt a user for input when running a query in Microsoft Access.Before diving into parameter queries, it's important to understand query criteria. If this concept is new to you, I recommend watching my introductory video on query criteria first. You can find a link to it below. In a basic query, we can filter information by entering a specific value as the criteria. For example, if I want to view customers from New York, I type "New York" (with quotes) into the Criteria row under the State field. When I run the query, only customers from New York will appear. However, this method is limited. If you want to look up customers from other states like Florida or Pennsylvania, you either have to edit the query in design view every time or create multiple queries for each state. Both options are inconvenient, especially if you need to make frequent changes. This is where parameter queries are useful. A parameter query uses a prompt inside square brackets instead of a fixed value. For example, if I place [Enter the state] as the criteria for State, running the query will produce a dialog box that says "Enter Parameter Value: Enter the state." The user can then type any state name, such as New York, Texas, or any other, and see the corresponding records. This approach is very flexible. The user can rerun the same query and enter a different state each time, without the need to change the query's design. You can save this as something like "My Customer Query." You could even create a button to launch it, or run it from the navigation pane. When prompted, just type the desired value, and you'll see matching records. For example, if you enter Florida, you get Floridians. Enter New York, you see New Yorkers. If you search for Texas and have no Texas customers, it will show no results. Parameter queries are not limited to exact matches. You can also use inequalities, such as greater than signs. For instance, entering >[Greater than start date] as the criteria for an order date field will prompt for a start date, then display all orders placed after that date. You can use the BETWEEN keyword too. Specify [Start date] and [End date], and Access will ask for each value, then show records between those two dates. Be cautious though, because if your data includes times along with dates, you may need to use "greater than or equal to" for the start date and "less than" for the end date. The BETWEEN keyword may not behave as expected if times are included. I have a separate video that explains this topic in detail, which is linked below. It's also possible to use parameter queries with wildcard searches using the LIKE keyword. For example, you can set the criteria to LIKE [Last Name] to allow searching for partial names. This lets users find records even if they only know part of the name. I have a full video explaining how to use LIKE and wildcards if you want to learn more. Sometimes, instead of typing the parameter directly in the query each time, you might want to collect information from a form. You can set up a form with a text box, then use a button to run your query so that it reads the value from the form's field. I have a video that covers this technique as well. All of these videos are available for free on my website and YouTube channel. Now, if you ever see the "Enter Parameter Value" box show up unexpectedly when you run a query, it's usually because something is misspelled, either a field name or an expression. If you run into this issue, check out my video dedicated to this common problem. For those interested in more advanced features, Access also offers a Parameters dialog box. You can define your parameters and specify data types, such as making sure a start date is recognized as a date. If a user tries to enter text instead of a date, Access will warn them. This feature is discussed in more depth in my Access Beginner Level 9 class. That sums up the basics of using parameter queries in Microsoft Access. If you want step-by-step instructions and demonstrations of everything mentioned here, visit my website for the complete video tutorial. Live long and prosper, my friends. Topic ListUsing parameter prompts in Access queriesSetting up a parameter for user input Running queries with user-entered criteria Creating exact match parameter queries Using inequalities as parameter criteria Using BETWEEN for date range parameters Understanding limitations with times in dates Using LIKE and wildcards in parameter queries Troubleshooting unexpected parameter prompts Overview of the Parameters dialog box for data types |
||||||||||||||||||||||||||||||||
|
| |||
| Keywords: FastTips Access creating a parameter query in access, Query Parameters, inequalities, criteira, Use parameters to ask for input when running a query, between, wildcards, like, Create a parameter query, Combine parameters with wildcards, Use parameters in PermaLink Parameter Query in Microsoft Access |
