'No Current Record' error in Group By query with outer joins - SOLUTION

[Allen Browne]

This problem has been posted several times without a solution. This post is
really so that anyone seaching at groups.google.com can find an answer.

JET can't handle Nulls in yes/no fields. If you create a query with outer
joins, the yes/no field can return nulls. If you then try to GROUP BY the
yes/no field, JET comes unstuck, and responds with a 'No Current Record'
error.

The solution is to use Nz() to substitute False for null, so the GROUP BY
clause contains:
Nz([MyYesNoField], False)

Note that there are many other causes of this same error message in other
contexts, e.g.:
- A bug in Access 2002 SP3, during the deletion events of the form.
(Workaround: trap and ignore the error.)

- Attempting to Move records in a recordset that has no records. (Solution:
test the RecordCount.)

- Referring to a record when BOF or EOF are true. (Solution: test BOF or
EOF.)

- Attempting to use the record after a Find without testing if the find
worked. (Solution: test NoMatch.)

- A corrupted index.

Hope that helps someone.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.