"Group By" problem

[Guest]

We have a query that has been working quite happily for over ayear, but has
suddenly decided to play up, giving a "No current record" message box. The
query is a Group By type, with just one of the items (hours) being "Sum". If
we remove the Group By, the query works fine.

We suspect a data problem, but cannot pinpoint it. Null is permitted in the
hours field, and has been there in the data in the past. Any clues, please?

JMCS

 

[Guest]

Hi.

The only time I've seen this happen with a query is when the table's index
was corrupted. However, Jet will apparently choke on a Yes/No field in a
GROUP BY query under special circumstances. (From your description, it
sounds like there's only one column in your query, an aggregate function on a
numerical data type, so this probably doesn't apply to you but it's something
for future reference.) For more information on this, please see the
following Web page:

http://groups.google.com/group/micr...public.access.*&rnum=1&hl=en#9b39994fe0c3a15c

Have you tried a compact/repair recently? It's worth a try. If that
doesn't help, try importing the tables and this query into a new database,
remove the indexes, compact/repair, add the indexes again, and then run the
query to see if you receive the same error in the new database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks - I think you have pointed us in the right direction because - guess
what - we do have "Yes/No" fields in the query (I didn't mention it because I
had assumed it was the field that was summed which caused the problem) and if
we take those out, it works OK, albeit missing a little information which we
can get another way. I will follow up the links you supplied. In the
meantime thanks again.

JMCS

 

['69 Camaro]

You're welcome. I considered leaving out the information about grouping on
the Yes/No field, because it didn't sound like it was pertinent to your
situation. Now I'm glad I mentioned it. ;-)

Good luck.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.