Troubleshooting

Missing Required Fields

I created a query that shows the tentative append record, but when I try to add a new record, I receive a "The field 'FieldName' can't contain a Null value because the Required property is set to True" error message. I don't want FieldName in the query.

You must include in your query result set all columns whose Required property value is set to Yes. This means, of course, that each of these fields must have a value typed in it. A unique primary key value is required to add a new record to any table with a primary key. For example, attempting to add a new record to a query on the Customers table that doesn't include CustomerID and CustomerName columns fails, because CustomerID is the primary key and CustomerName is a required field of the table.

Non-updatable Summary Queries

I can't update data in my summary query.

Summary queries aggregate data, so there's no direct relationship between the content of a query row and records in the underlying table(s). This means that there's no way for changes to aggregate values (dates, totals, averages, and the like) to propagate back to the table records. If you want to fudge the figures, change your select summary query to a make-table summary query, and then alter the data in the new table.

Problems with Null Values in Parameter Fields

When I enter an empty parameter value to return records without an entry in the specified field, the query returns no records.

You must edit the Jet SQL statement for the query to include an OR FieldName IS NULL expression to the WHERE clause to return records for fields with missing values. For example, in a query against the Orders table with Region and Country parameters, you must add OR Customers.Region IS NULL to return records for Germany and other countries whose Region values are missing. Following is the SQL statement that corrects the missing records for Germany problem:

 SELECT Customers.ContactName AS Contact,   Customers.CompanyName AS Company,   Customers.Address, Customers.City,   Customers.Region AS State,   Customers.PostalCode AS ZIP INTO tblUSMailList FROM Customers WHERE Customers.Country=[Enter the country name:]   AND (Customers.Region=[Enter the state code:]   OR Customers.Region IS NULL) ORDER BY Customers.PostalCode; 

In the preceding SQL statement, non-essential parentheses added by Access (not Jet) have been removed. The parentheses surrounding the (Customers.Region=[Enter the state code:] OR Customers.Region IS NULL) expression are required to group the two condition expressions into a single criterion. This issue exemplifies the importance of learning SQL while you're gaining experience with query design.