Not updating access query for null
The WHERE is executed first - before aggregation; the HAVING is executed afterwards - when the totals have been calculated.It makes sense, then, to put your criteria in the WHERE clause, and use the HAVING clause only when you must apply criteria on the aggregated totals. When you add a field to the design grid, Access sets the Total row to Group By, and the temptation is type your criteria under that.It is still worth the effort to create the best queries you can, so they don't suddenly slow down when you modify them.Totals queries (those with a GROUP BY clause) can have both a WHERE clause and a HAVING clause.When searching for blank fields, use the "Null" expression to query blank fields. Click the "File" tab and select "Open." Search the files and locate the database. Click a table name and the "Add" button to add the table to the query.Click the "Close" button to close the Show Table dialog box.In most cases, a stacked query will be faster yet (i.e.another saved query that you include as a "table" in this query.) There are times when a domain aggregate function is still the best solution you have (e.g.
Query criteria lets you refine the results by specifying for what you are looking. Select "Query Design." The Show Table dialog box appears.The visual clue that JET is treating the column as Text is the way it left-aligns. If you expected a numeric or date column, you now have serious problems. So 2 is greater than 19, because the first character (the 2) is greater than the first character of the other text (the 1 in 19.) Similarly, 4/1/2009 comes after 1/1/2010 in a Text column, because 4 comes after 1.Alarm bells should ring as soon as you see a column left-aligned as Text, when you expected it handled numerically.For every record, JET makes a VBA function call, gets the result, and then scans the entire table to eliminate the records from other years. My Date Between #1/1/2008# And #12/31/2008#, but this misses any dates on the final day that have a time component.) Particularly in criteria or sorting, avoid VBA calls so JET can use the index. The Client ID is hidden, and Surname and First Name are concatenated into one column so the full name is displayed even when the combo is not dropped down. Sort by the two fields, so JET can use the indexes on the fields to perform the sorting.Without the function call, JET could use the index to instantaneously select the records for 2008. The JET query optimizer is very good, so you may find that simple queries are fast without the tips in this section.
(Note: JET's IIf() is much more efficient than the similarly named function in VBA.