How to "Select Bottom"?

digitalshepard

Registered User.
Local time
Today, 17:57
Joined
Jul 20, 2011
Messages
32
As many of you are likely aware, Access has a [Select TOP] feature but does not have a [Select BOTTOM] feature. Very annoying.

The work around I used had a query which sorted a field in descending order [ORDER BY table.Field DESC], and another query which used a [Select Top 10] and a [ORDER BY table.Field ASC]. This worked great in Windows XP with Access 2003, but breaks with Windows 7 and Access 2003. Back to square one.

Any ideas how I can [Select BOTTOM] that doesn't break in Win7?
 
I have Win7 and Access 2007 on this machine, and SELECT TOP... works fine with either sort option. Are you getting an error, or ?
 
The error is "Option invalid or no longer set", but only with Windows 7 under Access 2003.

To restate the problem, I am trying to select the bottom 13 records from a query, but there is no SELECT BOTTOM in Access. My work around was as follows:
Code:
Query1: ORDER BY table.Field DESC
Query2: SELECT TOP 13
            ORDER BY table.Field ASC

This worked under XP and Access 2003, but gives an error under Windows 7 Access 2003. At this point, I have no idea what I can do to solve this.
 
That sounds like a VBA error. Is there any code running, a function being called, etc? I think I can say that the TOP should still work with that combination. Can you post the db here?
 
I have uploaded a sample db having the above mentioned issues. The 2 MonthlyData queries give errors in W7 but not XP. No VBA.
 

Attachments

Both open fine in Win 7/Access 2007 and Win 7/2010. I don't have a machine with Win 7/2003 on it to test (I have VM's with several combinations, but not that one). Perhaps someone with that configuration will jump in and test. I'll put out some feelers.
 
The sample DB I posted has been downloaded 5 times now. Has anyone tested it on Windows 7 with Access 2003 and gotten it to work? I sure haven't.
 
I have uploaded a sample db having the above mentioned issues. The 2 MonthlyData queries give errors in W7 but not XP. No VBA.
Tried downloading this. Did not work because the Monthlydata queries referenced something called tt that was not in the downloaded DB.
 
Is there anyone with a Win 7 & Access 2003 setup who can verify this issue? I've poked at it periodically and have yet to find a work around.
 
I just tried your MonthlyData queries on 2003 on Vista and I get the message on both queries.
"Option invalid or no longer set"


I signed on my XP machine Acc2003, tried your monthly queries and both work.
No error message.
 
Last edited:
jdraw,

That's what I've been getting as well. It sounds like this is a verified bug for this setup. I wonder if there is a work around?
 
If I get time I'll drag out my laptop and try the Win7/A2003 combo.
 
I get the error in A2003 on Win7. I'll see if I can figure out why.....
 
GPGeorge - I checked out Brent's post and am thoroughly amazed that this fixes the bug.

Thank you very much.
 

Users who are viewing this thread

Back
Top Bottom