Troubleshoot queries (MDB)

• You may have typed incorrect syntax in SQL view.
• You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

  Why you should avoid mixing queries created under different ANSI SQL query modes

  The two ANSI SQL query modes, ANSI-89 and ANSI-92, are not compatible. When you create a Microsoft Access database, you need to decide which query mode you are going to use, because mixing queries created in both query modes could produce runtime errors or unexpected results. The range of data types, reserved words, and wildcard characters are different in each query mode.

  Example of a query using wildcard characters

  A query that uses wildcard characters in a criteria expression can produce different results under each query mode. For example, if you run:

  • An ANSI-89 SQL query in a database set to ANSI-92 query mode, such as:

    SELECT * FROM Customers WHERE Country Like 'U*'
    								

    It returns all customers from a country/region named "U*", not all countries/regions beginning with the letter "U", because the asterisk (*) is not a wildcard character in ANSI-92 SQL.

  • An ANSI-92 SQL query in a database set to ANSI-89 query mode, such as:

    SELECT * FROM Customers WHERE Country Like 'U%'
    								

    It returns all customers from a country/region named "U%", not all countries/regions beginning with the letter "U", because the percent sign (%) is not a wildcard character in ANSI-89 SQL.

  Example of a query with a duplicate field and alias name

  If a query uses an alias that is the same as a base column name and you create a calculated field using the ambiguous name, the query will produce different results under each query mode. For example, if you run the following query:

  SELECT Orders.OrderID AS Freight, Orders.Freight, [Freight]*1.1 AS [New Freight] FROM Orders;

  Under ANSI-92 SQL, Access calculates the New Freight value using the Freight field, and therefore increases each Freight by 10%. Under ANSI-89 SQL, Access calculates the New Freight value using the Freight alias, and therefore increases each OrderID by 10%.

  How to avoid problems caused by mixing queries under different ANSI SQL query modes in the same database

  In general, avoid doing the following to prevent problems caused by mixing queries created under different ANSI SQL query modes:

  • Converting an Access database set to ANSI-92 SQL query mode from 2002 - 2003 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 - 2003 file format.
  • Changing the ANSI SQL query mode for the current database after you've created one or more queries. If you must do this, retest the existing queries to ensure that they still run or produce expected results, and rewrite the queries if necessary.
  • Importing queries created under one ANSI SQL query mode into an Access database set to another mode, or exporting queries created under one ANSI SQL query mode to an Access database set to another mode.
  • Changing the ANSI SQL query mode in an existing application. This could break your application and require rewriting your application.

    In Access 2000, you can only programmatically change the ANSI SQL query mode and any queries created under ANSI-92 mode were hidden in the Database window. In Access 2002 or later, you or a user can change ANSI SQL query mode through the user interface and ANSI-92 queries are no longer hidden in the Database window, so you should prevent accidental or intentional changes to the ANSI SQL query mode of your application by helping protect your code and preventing the changing of the query mode through the application's user interface.

  • Using ambiguous aliases and column names. To avoid confusion, ensure that aliases and column names are always unique in an SQL statement.

You may encounter this message if you didn't explicitly declare in the Query Parameters dialog box the parameters used for a crosstab query or a query that a crosstab query or chart is based on. To resolve this, do the following:

1. In the query that contains the parameter, specify the parameter and its data type in the Query Parameters dialog box.

   How?

   1. On the Query menu, click Parameters.

   2. In the first Parameter cell, type the first prompt you entered in the query design grid.

   3. In the Data Type cell to the right, click the appropriate data type according to the following guidelines:

      If the parameter field data type is	Click this data type in the Data Type cell
      Currency, Date/Time, Memo, OLE Object, Text, and Yes/No	Currency, Date/Time, Memo, OLE Object, Text, and Yes/No
      Number	Byte, Single, Double, Integer, Long Integer, and Replication ID. These data types correspond to the five FieldSize property settings of the Number data type.
      Unknown	Value. This is a generic data type that accepts any type of data.
      Binary	Binary. Used with parameter queries directed to linked tables that do recognize this data type (Microsoft Access does not recognize this data type).

   4. Repeat Steps 2 and 3 for each parameter you want to specify a data type for.

2. In the same query, set the ColumnHeadings property.

   How?

   You can change the order or limit the column headings in a crosstab query. For example, in column headings containing the months of the year, you can display the months chronologically rather than alphabetically. Or, you can limit the columns to just January through June.

   1. Open the crosstab query in Design view.
   2. Click the background of query Design view, outside the design grid and the field lists.
   3. On the toolbar, click Properties to display the query's property sheet.
   4. In the ColumnHeadings property box, enter the column headings you want to display, in the order in which you want to display them. Between the column headings, type a comma or the list separator for your country/region. (To find the list separator for your country/region, see the regional settings in Microsoft Windows Control Panel.)

      The column headings you enter must exactly match the column headings in the query datasheet. For example, if a column heading in the datasheet is "USA," you must enter a column heading of "USA"— not "US." (After you press ENTER or move the pointer to a different location, Microsoft Access places quotation marks around each heading.)

   5. To view the query's results, click View on the toolbar.

   Notes

   • If you run a crosstab query often, or if you use it as the basis for a form, report, or data access page, you can speed up the query by using the preceding procedure to specify fixed column headings.
   • If you frequently use the same column headings in different queries, consider creating a table with one Text field to store the column headings. Then open the table and copy the headings into the ColumnHeadings property box as needed.

A key violation occurs if:

• You attempt to run a query that appends, deletes, or updates records in a way that violates the rules of referential integrity for related tables.
• Your query attempts to append or update records that contain primary key values that already exist in the destination table.

If you run such a query, it will not modify the records that cause the key violation.

To modify records in a way that will violate referential integrity, you can break the relationship between the affected tables or turn referential integrity off, and then modify the affected tables one at a time. However, the resulting data in the two tables may then conflict.

A validation rule violation occurs if you attempt to update or append records that violate the validation rules for a field or record.

When you see the following message:

"The SQL statement couldn't be executed, because it contains ambiguous outer joins."

You tried to execute an SQL statement that contains multiple joins. There are three types of joins in Microsoft Access: inner joins (also called equi-joins), left outer joins, and right outer joins. In some multiple joins, the results of the query can differ depending on the type of join and the order in which the joins are performed.

Consultants LEFT OUTER JOIN Active Consultants INNER JOIN Projects
								

• Verify that the criteria you specified is for the same data type as the data in the underlying table or query. For example, the field ReorderLevel has a Number data type. Therefore, if you type the criteria "50", you'll get an error because Microsoft Access interprets values in quotation marks as text, not numbers.

  Other situations that cause a data type conflict include:

  • You're specifying criteria for a Lookup field and the criteria uses the values that display on the lookup list instead of their associated foreign key values. Because the foreign key values are the values actually stored in the underlying table, you should use them when you specify criteria for a field.
  • You typed the dollar sign ($) in criteria you specified for a Currency field. Remove the dollar sign, and then view the results.
• Verify that the data type of each pair of joined fields in the query is the same. If not, change the data type of one of the joined fields to match the data type of the other.

You may encounter this message if a parameter query exported to the IDC file type doesn't have data types specified in the Query Parameters dialog box.

1. In the Database window, click Queries under Objects.
2. Click the name of the parameter query you want to modify, and click Design on the Database window toolbar.
3. Specify a data type for the parameter.

   How?

   1. On the Query menu, click Parameters.

   2. In the first Parameter cell, type the first prompt you entered in the query design grid.

   3. In the Data Type cell to the right, click the appropriate data type according to the following guidelines:

      If the parameter field data type is	Click this data type in the Data Type cell
      Currency, Date/Time, Memo, OLE Object, Text, and Yes/No	Currency, Date/Time, Memo, OLE Object, Text, and Yes/No
      Number	Byte, Single, Double, Integer, Long Integer, and Replication ID. These data types correspond to the five FieldSize property settings of the Number data type.
      Unknown	Value. This is a generic data type that accepts any type of data.
      Binary	Binary. Used with parameter queries directed to linked tables that do recognize this data type (Microsoft Access does not recognize this data type).

   4. Repeat Steps 2 and 3 for each parameter you want to specify a data type for.

4. Save and run the query.

This error occurs when the current query's Update To row includes a field from either a crosstab query or select query in which an aggregate (total) was calculated for the field (using either the Totals row or a domain function in the Field row). To update a field using the aggregate of another field, calculate the aggregate in the update query itself, not a different query.

1. In query Design view for the current query, remove the field you added from the crosstab or select query (where an aggregate was calculated for the field).
2. In the Update To cell of the field you want to update, enter a domain function that calculates the same aggregate as is calculated for that field in the select or crosstab query.

   For example, if you had a calculated field named Sales So Far in your Products table, you could update it with the product of the Quantity and UnitPrice fields in the Order Details table by adding it to the design grid in the update query and then entering the following domain function in its Update To cell.

   DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])
   								

3. To run the query, click Run on the toolbar.
4. To see the results, open the table by clicking Tables under Objects in the Database window and clicking Open on the Database window toolbar.

You use wildcard characters as placeholders for other characters when you are specifying a value you want to find and you:

• Know only part of the value.
• Want to find values that start with a specific letter or match a certain pattern.

Wildcard characters are meant to be used with fields that have the Text data type. You can sometimes use them successfully with other data types, such as dates, if you don't use the Microsoft Windows Control Panel to change the regional settings for these data types.

If you are using Microsoft Jet database engine SQL in a Microsoft Access database, you can use the following wildcard characters in queries and expressions to find such things as field values, records, or file names. You can also use these characters in the Find and Replace dialog boxes in an Access database or a Microsoft Access project.

To find such things as field values, records, or file names in a Microsoft Access project, or in a Microsoft Access database that uses Microsoft SQL Server-compatible syntax, use the following ANSI-92 wildcards.

Note  If you are using the Find and Replace dialog boxes in an Access project or in an Access database that uses Microsoft SQL Server-compatible syntax, you must use the Microsoft Jet SQL wildcards.

Notes

• When using wildcard characters to search for other wildcard characters such as an asterisk (*), question mark (?), number sign (#), opening bracket ([), or hyphen (-), you must enclose the item you're searching for in brackets. If you're searching for an exclamation point (!) or a closing bracket (]), you don't need to enclose it in brackets.

  For example, to search for a question mark, type [?] in the Find dialog box. If you're searching for a hyphen and other characters simultaneously, place the hyphen before or after all the other characters inside the brackets. (However, if you have an exclamation point (!) after the opening bracket, place the hyphen after the exclamation point.)

• You can't search for the opening and closing brackets ([ ]) together because Microsoft Access interprets this combination as a zero-length string. You must enclose the opening and closing brackets in brackets ([[ ]]).

• If tables or queries in a query aren't joined to one another, either directly or indirectly, Microsoft Access doesn't know which records are associated with which, so it displays every combination of records (called a "cross-product" or "Cartesian product") between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10 X 10). It also means the query might take a long time to run and ultimately might produce meaningless results. To prevent this, create a join between the tables or queries.

• If tables or queries are already joined, check the join properties of each join line to see what type of join is in effect. Join properties determine whether one table or query contributes all of its records or only those records that match records in the other table.

• Add criteria to limit the records that the query retrieves. Only records that meet the criteria you define are retrieved.
• If you already specified criteria, you may want to specify additional criteria, either for the same field using the And operator, or for other fields. Also, verify that existing criteria is entered correctly. Check for misspellings, unnecessary spaces, or extra characters. Verify that you set the criteria for the appropriate field and that it's the criteria you want the records to meet. If you're using alternative criteria in the Or row in the query design grid or in criteria expressions that use the Or operator, you may want to remove it.
• Check the data you're trying to find. If the data contains extra spaces or other special characters, the criteria must account for this. You can use wildcard characters in the criteria if you aren't sure of or don't want to specify the exact field values. If you're already using wildcard characters and not getting the records you want, you may be using the wildcard characters on the wrong data type.
• In a select, append, or make-table query, if you're using Top Values on the toolbar in query Design view (or setting the TopValues property in the query's property sheet) to return a specified number of the highest or lowest values in a field, the query might return more records than the number you specified. This is because all records that match the last record are also returned. Therefore, a query would return all three of the following records even if you requested to see the employees with the top two sales totals because the query doesn't distinguish between the two 50,000 sales totals.

  EmployeeID	Sales
  1	60,000
  2	50,000
  3	50,000

  If you don't want duplicate values to be returned, set the UniqueValues property in the query property sheet to Yes.

• If the query includes more than one table or query, check the join properties of each join line between them. Join properties determine if a table or query contributes all of its records or only those records that match records in the other table.

• If the type of join you're using retrieves only matching records from the two joined tables, any records in which the joined field contains a Null value won't be included in the query's results. You can use the Nz function to convert Null values to a zero, zero-length string, or other specified value so that those records are included in the query's results.
• If you specified criteria, it may be too restrictive. For example, if you specified criteria in the Criteria row for two different fields, both criteria must be true for a record before the query retrieves it. You may want to delete some or all of the criteria to allow the query to return more records.
• If you have entered criteria that combine two or more expressions with an OR operator on more than one criteria row, you must repeat any other expressions in other fields for each criteria row you have used in order for those other expressions to apply to all criteria. For example, if your combined criteria for a ShipCountry field is "Canada" OR "UK", and you want to limit the ExtendedPrice field to <10000 for both countries, enter all the criteria on one criteria row, or repeat the <10000 expression for each criteria row you use.
• Verify that existing criteria is entered correctly. Check for misspellings, unnecessary spaces, or extra characters. Verify that you set the criteria for the appropriate field and that it's the criteria you want the records to meet.
• If a field name includes a colon, you must enclose the name in brackets whenever you refer to the field in an expression. Otherwise, Microsoft Access interprets the field name as a literal text value, and therefore won't retrieve the records you want.
• Use wildcard characters in the criteria if you aren't sure of or don't want to specify the exact field values. If you're already using wildcard characters and not getting the records you want, you may be using the wildcard characters on the wrong data type.
• Add alternative criteria to select the records you want to work with. You can do this in the query design grid in the Or row, or in the Criteria row using expressions containing the Or operator.
• If you're using criteria to select records containing specific values in a field or fields, any records with Null values in that field don't match the value, and therefore won't be included in the records that are returned. To include records containing Null values, type Is Null in the Or row.

• Check the data you're trying to find. If the data contains extra spaces or other special characters, the criteria must account for this. You can use wildcard characters in the criteria if you aren't sure of or don't want to specify the exact field values. If you're already using wildcard characters and not getting the records you want, you may be using the wildcard characters on the wrong data type.
• Verify that existing criteria is entered correctly. Check for misspellings, unnecessary spaces, or extra characters. Verify that you set the criteria for the appropriate field and that it's the criteria you want the records to meet.
• Verify that the tables and queries you've added to the upper part of query Design view are the correct ones.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

• Remove the fields in the query design grid that you don't want to see in the query results.
• Remove the asterisk (*) if you added it to the design grid. The asterisk automatically includes all fields from the underlying table. If you want to keep the asterisk (*), clear the Show check box for all fields except the asterisk; otherwise those fields will appear twice in the query's results.
• Hide fields that you don't need to display in the query's results.
• Verify that the Output All Fields check box isn't selected. (To see if it is selected, on the Tools menu, click Options, and then click the Tables/Queries tab.) If it is, all fields from the underlying table or query will appear in the query's results without being added to the query design grid. If you then add a field to the design grid, say, for setting criteria, you need to clear the Show check box for that field; otherwise, the column appears twice in the query's results. If you want, clear the Output All Fields check box. Changing this option affects the property setting for new queries you create, but it doesn't affect existing queries.

• Add more fields to the query design grid or, if you add the asterisk (*) to the design grid, the query automatically includes all fields from the underlying table or query.
• Add other tables or queries, then add the other fields you need.
• Show hidden fields.
• If the Output All Fields check box is not selected, only the fields in the design grid are retrieved. (To see if it is selected, on the Tools menu, click Options, and then click the Tables/Queries tab.) If it is, all fields from the underlying table or query will appear in the query's results without being added to the query design grid. If you then add a field to the design grid, say, for setting criteria, you need to clear the Show check box for that field; otherwise, the column appears twice in the query's results. Or, if you want, clear the Output All Fields check box. Changing this option only affects the property setting for new queries you create, not existing queries.

• In the query design grid, remove the fields that you don't want the query to retrieve.
• If columns are missing, add their fields to the query design grid.
• Verify that the tables and queries you're retrieving records from are the correct ones.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

• If there are records with Null (blank) values in the field you are calculating, those records won't be included in the calculation.
• If the query isn't performing the calculation on the correct groups, verify that you have Group By in the Total cell for the field or fields you want to use for grouping calculations. Also, where you specify criteria in the query design grid determines if records are excluded before the query groups or performs calculations, if groups are excluded before the calculation is performed, or if certain results are excluded after the calculation is performed.
• In a crosstab query, you might have too many or too few Group By fields. Add, remove, or change the fields designated as row headings that group the totals vertically. Verify that you're using the correct field's values as column headings that group the totals horizontally.
• Verify that the aggregate you selected is the correct one. If not, select a different aggregate from the list in the Total cell.

In the query design grid, delete the aggregate you specified in the Total cell for that field (column), and then select one in a different field.

If you use an arithmetic operator (such as +, -, *, /) in an expression, such as UnitsInStock] + [UnitsOnOrder], and one of the fields in the expression contains a Null value, then the result of the entire expression will be a Null value. To avoid this, you can convert Null values to zeros or other numbers by using the Nz function.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

For AutoLookup to work, certain conditions must be met:

• The query must be based on more than one table and the tables must have a one-to-many relationship. (Referential integrity doesn't have to be enforced.)
• The join field on the "one" side of the relationship must have a unique index. A unique index means that the field is a primary key or its Indexed property in table Design view is set to Yes (No Duplicates).
• The join field you add to the design grid must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key for that table.) For AutoLookup to work, this field can't be a primary key and its Indexed property can't be set to Yes (No Duplicates). For example, in a query that includes the Customers and Orders tables, drag to the design grid the CustomerID field from the Orders table, not the one from the Customers table.
• The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.

Even if you haven't created relationships between tables, Microsoft Access automatically creates joins if you add two tables to a query and the tables each have a field with the same or compatible data type and if one of the join fields is a primary key. You can enable or disable these automatic joins.

1. On the Tools menu, click Options.
2. Click the Tables/Queries tab.
3. Select or clear the Enable AutoJoin check box.

Note   This setting applies to new queries only.

In some cases, you can edit data in query Datasheet view to change the data in the underlying table. In other cases, you can't. The following information shows whether a query's results can be updated, and if not, whether there is an alternative.

If you get prompted to type parameters when you attempt to run a query, report, or data access page, and you don't expect this prompt, one of the following might apply:

• You deleted a parameter from the query design grid but not from the Query Parameters dialog box.
• You renamed or misspelled a field in the query, report, or data access page but not in the query's underlying table(s).
• You have a field that refers to a calculated field. If a field in the query performs a calculation based on the calculated value, make sure the Show check box is selected for the calculated field in the query design grid.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

If you get prompted to type parameters when you attempt to run a query, report, or data access page, and you don't expect this prompt, one of the following might apply:

• You deleted a parameter from the query design grid but not from the Query Parameters dialog box.
• You renamed or misspelled a field in the query, report, or data access page but not in the query's underlying table(s).
• You have a field that refers to a calculated field. If a field in the query performs a calculation based on the calculated value, make sure the Show check box is selected for the calculated field in the query design grid.

1. In the design grid under the field that contains the prompt in the Criteria cell, type [prompt] Is Null in the Or cell, where prompt is the same prompt that is in the Criteria cell for that field. For example:

   In this cell	Type
   Criteria	Like [Enter Category ID:]
   Or	[Enter Category ID:] Is Null

2. Run the query.

3. Leave the Enter Parameter Value dialog box blank, when Microsoft Access prompts you.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

You can set the query's ColumnHeadings property to specify which column headings are displayed and in what order you want them to appear.

Data in a crosstab query is not updatable.

Microsoft Access returns "<>" as the column name for any NullNull value in the field with Column Heading in its Crosstab cell. To avoid this you can:

• Explicitly define your column headings by setting the query's ColumnHeadings property.

  Sort or limit column headings displayed in a crosstab query

  You can change the order or limit the column headings in a crosstab query. For example, in column headings containing the months of the year, you can display the months chronologically rather than alphabetically. Or, you can limit the columns to just January through June.

  1. Open the crosstab query in Design view.
  2. Click the background of query Design view, outside the design grid and the field lists.
  3. On the toolbar, click Properties to display the query's property sheet.
  4. In the ColumnHeadings property box, enter the column headings you want to display, in the order in which you want to display them. Between the column headings, type a comma or the list separator for your country/region. (To find the list separator for your country/region, see the regional settings in Microsoft Windows Control Panel.)

     The column headings you enter must exactly match the column headings in the query datasheet. For example, if a column heading in the datasheet is "USA," you must enter a column heading of "USA"— not "US." (After you press ENTER or move the pointer to a different location, Microsoft Access places quotation marks around each heading.)

  5. To view the query's results, click View on the toolbar.

  Notes

  • If you run a crosstab query often, or if you use it as the basis for a form, report, or data access page, you can speed up the query by using the preceding procedure to specify fixed column headings.
  • If you frequently use the same column headings in different queries, consider creating a table with one Text field to store the column headings. Then open the table and copy the headings into the ColumnHeadings property box as needed.
• In the design grid, type Is Not Null in the Criteria cell of the designated Column Heading field.
• In the Criteria cell of the designated Column Heading field, use the Nz function in an expression that will convert Null values to a more meaningful value, such as "Unknown."

Verify that the field whose values you want to use as column headings is in the query design grid with Column Heading in the Crosstab cell, and that the field or fields whose values you want to use as row headings are in the query design grid with Row Heading in the Crosstab cell. To change the value in a Crosstab cell, click the cell, and then click a value from the list. If you want to display only certain values as column headings, set the query's ColumnHeadings property.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.

Verify that you have selected the correct fields for the data you want to append, entered the correct criteria in the query design grid, and selected the correct fields to which you want to append data. Restore the appended query to its original state, through a backup copy if one is available, then do one or more of the following:

Note  If you added the asterisk (*) in the Field cell, select the asterisk in the Append To cell. All the field names in both tables must be exactly the same. You can't specify criteria for selecting records in the column that contains the asterisk, but you can specify criteria in a separate column.

• You need to specify a different update by changing the expression in the Update To cell. For example, to raise salaries by 5 percent, type [Salary] * 1.05 in the Update To cell of the Salary field.
• Verify that you're updating data in the correct field. If you're updating data in the wrong field, specify an update for a different field. To specify an update for a field, type the expression you want in the Update To cell.

Delete the duplicate record or the original directly in query Datasheet view.

If you want Microsoft Access to delete all the duplicates and keep the original records automatically, don't use the Find Duplicates Wizard or convert a Find Duplicates query to a delete query.

Make sure the delete query includes:

• The table you want to delete records from. Drag the asterisk (*) from the field list for that table to the query design grid.
• The field(s) you want to specify criteria for. Drag the field you want from the table's field list to the design grid.

In the query design grid, From or Where is displayed in the Delete row. From is displayed in the Delete cell of the table you'll delete records from, and Where is displayed in the Delete cell of the field(s) you specify criteria for.

You created your query under a different ANSI SQL query mode than the current mode of your Microsoft Access database.