sorting by date-some work, some don't

  • Thread starter Thread starter kp
  • Start date Start date
K

kp

Access 2002. Db built on Access 2k. Currently at 262 MB.
I have built 6 queries from 6 different tables. The subject matter for each
is different, otherwise the tables and queries are virtually identical.

However, 1 query will not sort dates in descending order. I have gone over
and over the table and query and can find nothing that makes it different
from the other 5 for this particular field...yet it absolutely will only
leave the dates in ascending order. If don't tell it to sort at all, they
are in ascending order. If I tell it to do ascending order, it remains in
ascending order.

Any ideas?
 
Let's clarify exactly what's happening here:

A) We are talking about data stored in Access tables (not attached tables
from Oracle or Excel or something).

B) If you open the table in design view, this field is a Date/Time field
(not a Text field).
If either #1 or #2 is different, we need to take a different tac.

C) In query design, double-check that no other fields have anything in the
Sorting row under them - especially any fields to the left of the date
field.

If that all checks out, then the problem is probably a corruption. It could
be a corrupted index, or it could be a Name AutoCorrect failure.
Suggestions:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
For an explanation of why, see:
http://members.iinet.net.au/~allenbrowne/bug-03.html

2. Compact and repair the database:
Tools | Database Utilities | Compact
That process will dump the Name AutoCorrect guff, and it will also repair
any damanged index.

3. Open the problem query. Switch to SQL View (View menu). Copy the statment
you see there, and paste it into (say) Notepad. Delete the query. Compact
the database again. Then create a new query, switch it to SQL View, and
paste the SQL statement back in.

That should solve the problem.

4. If it is still broken, open the table in design view. Select the
date/time field. In the lower pane, what is the setting for Indexed? If
"No", try, "Yes, Duplicates Ok". If Yes, try No. Then compact again and see
if that sorts it.

5. If it is still broken, you could rebuild the database by following the 6
steps for the first symptom in artilce:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html
 
Allen,
Yes the data is stored in Access tables, and the field is date/time.

Thanks for the suggestions. I'm going to go through each one in order. I'll
let you know my results.
Thanks again for your time!
kp
 
Thanks Allen...I'm working on these items...just haven't had time lately. I
just wanted to let you know I read your response right away on the 28th, but
haven't had a chance to respond. I'll keep you posted.
kp
 
Allen, if you're still there...here is what I've done:
1. The Name AutoCorrect box had not been checked, so I skipped all that.
2. I compact and repair quite often had just done that, so I skipped this.
3. I've done this and the problem still persists
4. I did change the "yes, duplicates ok" to "no" and that solved the same
problem with another table, but not this one.

So that leaves "recovering from corruption...which I haven't looked at, but
it sounds pretty scary for a minor problem. I may take a look, I may not.

But I do want to thank you for your time on this!
kp
 
One more comment...just looked at "recovering from corruption" and none of
the stuff seems to apply to my scenario. So we may chalk this up as a
'feature'!
k
 
Back
Top