Create a query that accepts "Start" date and "End" date as parameters
In Access 2003/XP/2000/97, I'm trying to run a query based on a date. When the query opens, I would like some type of window prompting for the user to enter a "start" date and an "end" date. How can I do this?

---

Create a Query in Microsoft Access Using the LIKE Operator
You can create a query to search data in a table by using the "LIKE" parameter. The "like" syntax allows you to perform wildcard searches against text strings.

---

Fascinating Query Tricks
I love writing VBA code but I am convinced that, many times what I am trying to accomplish can be done in a more efficient, graceful and elegant way using SQL queries. Over the last few months, I have tried to develop my applications with this in mind. The following article is a summary of problems I have been able to solve by using fascinating query tricks. Here is an overview of what we will cover: * How to pass a variable parameter to a query calculation * How to add "<All Employees>" to a list of employee names * Use UNION query to add a Totals row to a dataset output

---

Create parameter queries that prompt users for input
A Microsoft Access parameter query is useful when you perform the same type of query often, but the values that you search for change. If you're familiar with the basic select query, learn how to turn it into a parameter query. After completing this course you will be able to: 1) Understand when and how to use parameter queries. 2) Set up and run a parameter query that prompts you for the data you want to find. 3) Set up a parameter query that retrieves all the data between two values. (Microsoft Office Access 2003)

---

Make a query ask for input
When you want a query to ask for input every time that you run it, you can create a parameter query (parameter query: A query in which a user interactively specifies one or more criteria values. A parameter query is not a separate kind of query; rather, it extends the flexibility of a query.). You can also create a form to collect parameter values. Using a form, you can: * Use data-type-specific controls * Enable the persistence of parameter values * Provide combo box controls (so that you can pick a value from a list, instead of typing it) * Use other controls that are available in forms This article provides an overview of parameters, explains how to use parameters in queries, and how to use forms to enhance your use of parameters.

---

What exactly does &quot;null&quot; mean in Database?
First I tried to look at it as a "zero" (with a little confusion though) when I came across it in some queries, or think about it as a text string of blank spaces. Null means either "not applicable" or "don't know": it's not really the same as zero (0) or any other default value, but more importantly, null is treated quite differently from other values in SQL, because it literally has no value.

---

Perform a stack rank in a query in Access 2003
Is there a way to write a formula in a query that would stack rank a group of numbers in a field? I have a table called "Data Table" with a field named "AHT" that contains whole numbers like 245, 352, and 536.

---

Parameter Input that Accepts Multiple Parameters
We will learn Parameter Input that Accepts Multiple Parameters.

---

Display a subtotal for each category within a report
In Access 2003/XP/2000/97, I'm trying to create a report that will display total hours at the end of each category. For instance, in my database I have the following fields titled: "computer" and "total hours". The values in "total hours" represent the number of hours that a technician has worked on a computer. I would like a total of hours for computer #1 displayed before the detail for computer #2 is displayed.

---

Update Query that updates one field based on the value of another field
In Access 2003/XP/2000/97, I have a table called DQListUpdate and I would like to update the [Area Number] field with the value "Mesa001" when the [St Zip] field is equal to 85201 or 85203. How can I do this?

---

How to Create a Crosstab Query
A "crosstab query" in Microsoft Access 2003 compresses selected data into a viewable space. This allows you to quickly locate values and trends. To create a crosstab query in Access, follow these instructions.

---

SQL Operators and Operands
In order to use a computer language, you usually give instructions to an internal program. An instruction can be formulated as a command. Examples are "give me food" or "touch your head". Another type of instruction can be formulated as a question. Examples are "what time is it?" or "Are you hungry?". The sentence or group of words that constitute an instruction is also called a statement.

---

MS-Access Paremeter Queries
Is it possible to create a parameter query, where if the parameter input box is left blank will return all of the records? This article details how, if you design your query using the following criteria, you will be able to query by either a specific value, or leave the parameter box empty to return all of the records: In the following example, we would like to add criteria to allow us to query for a Salesperson by region, but may also like to view all records as well.

---

Create a Parameter Query
A parameter query prompts you to provide data or information before the query is run. For example, you could query your database to show you the sales you have generated in the month of your choice. Each time you run the query, you will be prompted to identify the month that interests you. These instructions apply to Access 97.

---

Dynamic queries using parameters
A parameter query is a query in which the criteria for selecting records are determined when the query is executed rather than when the query is designed. Learning objectives: understand the way in which parameters can be used to create flexible queries; prompt the user to enter parameter values; create a query whose results depend on a value on a form.

---

Need help on MS Access
I am creating a database using access 2003 for myself. Everything I key in a new record I have to manually type in the name in the first field, it is time consuming if the name is long. So I created a drop down list. I wanted to get the data on my "name" field, so during the list creation I choose the option: "I want the list box to look up the values in a table or query"

---

Create a Query That Uses Multiple Criteria in One Field
Creating a query that uses multiple criteria for a field is like running a filter on your query results. For example, if you have a table that contains complete addresses of all your friends, you can run a query that will display brief information for those who live in New York, or for those who live in New York and whose surname starts with the letter "B".

---

Create a parameter query that performs a wildcard search
I'm trying to build a parameter query that allows the user to input desired text, and the query will return all records that CONTAIN that text.

---

Use the Wizard to Create a Find Duplicates Query
You can use the wizard to create a "Find Duplicates" query in Access 2003. The wizard lets you frame and implement your query with a few clicks of a mouse. Just open the database that you suspect contains duplicate fields, get the wizard working and in moments you’ll have the information you need.

---

How to create a Microsoft Access macro that opens/runs a query
hen creating databases for end users, the goal is to make the database user-friendly and intuitive. This can be done with the use of macros which are used to automate tasks within the database. This is handy for both the administrator(s) and the end-user(s). It allows the administrator the ability to "hand-off" the day-to-day operations of the database to others. The end-users do not need to be programmatically familiar with the database in order to use it. Macros and other features within Access allow this type of flexibility. This article provides step-by-step details regarding how to automate the opening and running of a query through the creation of a macro. By opening a query, it also runs it. At the conclusion of the "how-to" will be a macro that will open a query based upon the administrator’s desired outcome. Each time the macro is run, the query will run also.

---

Plan for Null Responses in Your Parameter Queries
Parameter queries in your database are a favourite with many users because they're efficient, easy to create, and they offer a dynamic solution when you want to query the same field for different criteria. For the most part, parameter queries work great until you decide not to limit the results of the query. For instance, suppose you want to return all of the records in a table. If you ignore the parameter request by entering nothing in response to the prompt, Microsoft Access doesn't return any records. In this article, we'll show you a simple way to avoid this behaviour.

---

The Query Lost My Records!
Nulls in Criteria: You wish to mail two different messages - one to your WA friends and another to those in other states. You create two queries: one where the Criteria line under State reads WA , and a second query where the Criteria line reads Not "WA" . You could think this would take care of all cases. It doesn't!

---

Rolling dates by pressing "+" or "-"
Code to increment or decrement a date by pressing the plus or minus key in a Microsoft Access database. Some commercial programs (Tracker, Quicken, etc) allow the user to press "+" or "-" to increment or decrement a date without the hassle of selecting the day part of the field and entering a new value. This is especially useful in fields where the default date offered could be a day or two different from the date desired. To provide this functionality in Access, attach this Event Procedure to the KeyPress event of your control.

---

Showing a Please Wait/Progress Macro
There maybe occasions where you will need to display a message to inform your user's that they must wait for an action or process to be completed. You may want to display the text "Database Loading" whilst Access initializes a large database, or display the text "Please Wait" whilst Access runs a long query.

---

Help with a SQL query? Multiple tables, but all I want to do
This is turning out to be trickier than I thought. Basically, what I'm trying to do is implement a Coldfusion search form on a basic "products" website. The business handles fruit juice products. So for instance, if the user searches for "apple", they'll get a list of all apple products - apple juice concentrate, apple essence, apple extracts etc. The CF portion is easy enough, I just need help with the SQL query. Here's the problem: the products are not all contained in one table. They CAN'T be, because different types of products require different types of specifications. So, I've got each product type in its own table. I want a SQL query that will search the PRODUCT_NAME column of all the tables for values containing the user's search term. I understand I'll need some kind of join for this, but the online resources I've looked at all seem to gloss over joins, which is surprising as they seem essential to database use.

---

Print Ms-Access Query Parameter values onto your report
If you've based an Access report on a parameter query, you can print the parameters as part of your report. For instance, if your parameter is a date, you can print that date in the header of your report. Simply add a text box to your report's header and specify the parameter by name as the control's Control Source using the form.

---

I get the error message "Could not delete from the specified tables"
When you run a delete query (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), Microsoft Office Access 2007 may display the error message Could not delete from the specified tables. This article lists cases in which you can use a delete query, explains why the error message appears, and provides steps for correcting the error.

---

Parameter Query Criteria
I successfully created two tables. Products table and supplier table. I created a list box in the supplier table for products listed in the product table. I created a parameter query from the supplier table. My problem is the criteria. I would like to create a report from the parameter query. I would like this report to have a list box to select a product from the product table which would go into the criteria of the parameter query to form the report. I have been all over the help in access and cannot figure out how to do this. Your help would really be appreciated.

---

Simple Queries
Queries are saved sets of instructions that allow users to view selective parts of data contained in one or more tables or other queries. These instructions are written in a language called Structured Query Language (SQL). Each time a query is "run" it selects for display from the data currently in the database so that if data is added and then a query is run, the new data will be displayed according to the instructions contained in the query. Tow major functions of queries are ordering and filtering.

---

Turn on or off automatic correction of text and data
Use AutoCorrect to automatically correct text that you frequently mistype, and to automatically replace abbreviations with long names. For example, you can create an entry "esq" for "Martin R. Esquivel." Whenever you type esq followed by a space or punctuation mark, Microsoft Access automatically replaces "esq" with the name Martin R. Esquivel. AutoCorrect works with text in a datasheet and with text in a form in Form View.

---

Display parameter values in a report based on a parameter query
In Access 2003/XP/2000/97, how do I display parameter values in a report based on a parameter query?

---

Include NULL values in query results
In Access 2003/XP/2000/97, I have a query that in which I try to exclude certain records based on an entry in a particular field (eg. something like "not C"). For some reason, Access also seems to strip out the NULL values as well. Do you know how I can also include the NULL values in the query results?

---

Forms, Access Databases and ASP Code
I have been working on this site for a while now, and there are a few things that I will need to do that I am unsure of. The first is that I want the users instead of Registering, to take a survey, at the end of which they will have been registered. To do this I want to keep the Survey Data, and the User Data Separate. I have below the Form Page, and the Submission page, cutting out the name of the database for security reasons. I need the data from the form, on submission to go into 2 table fields, and it's giving me some trouble. Worst of all, I would like to reference the Users ID Number, userid, from the "Users" Table, into the "Survey" Tables field userid. Among the many errors, I'm thinking there is an easier way to do all this than what I have done. Help is greatly appreciated

---

Calculated fields
A calculated field is a "virtual field" in a query. The field is virtual in the sense that it is not stored anywhere in the database. Instead, it is calculated dynamically when the query is used. Learning objectives: create a calculated field; understand why ACCESS add square; brackets around field names; understand the use of the ampersand operator (&). (pdf file, install Acrobat Reader to read this tutorial)

---

Sorting and Filtering Boolean Fields
A Boolean field is one that holds a true or false result and it is created as a Yes/No type. By default, and most of the time, a Boolean field displays a check box that can be checked (or marked) or cleared (or unchecked). By its Boolean interpretation, Microsoft Access considers that a check box that is checked is &quot;selected&quot; and a check box that is not checked in &quot;cleared&quot;. You can use this logic to sort the records of a table, a query, or a form.

---

Introduction Structured Query Language
This page is a tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is "ANSI", or standard SQL, and no SQL features of specific database management systems will be discussed until the "Nonstandard SQL" section.

---

Introduction to Structured Query Language
This page is a introductory tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is "ANSI", or standard SQL, and no SQL features of specific database management systems will be discussed until the "Nonstandard SQL" section.

---

Queries that change data
All of the queries that you have created to this point have been variations of "select" queries. Select queries are used to display data, but do not actually change the data in any way. In this lesson, you are going to learn about action queries. Learn: What is an action query? Why use action queries? Learning objectives: understand the difference between action queries and select queries; make a backup copy of a table using an action query; undo (rollback) an action query once it has been executed; update only certain records in a table; create a button on a form that executes an action query when pressed. (pdf file, install Acrobat Reader to read this tutorial)

---

PARAMETERS Declaration
Declares the name and data type of each parameter in a parameter query.

---

How to Limit Data in a Microsoft Access Field to Certain Values
Limiting data in a field prevents entry errors. For instance, suppose you are selling towels in three colors - red, blue, and yellow. Limiting the data values to "red," "blue" or "yellow" will prevent people from mistakenly ordering pink towels. These instructions apply to MS Access 97.

---