access ignoring result limit (select top x)

[churchers]

The following is a direct copy and paste from my intranet website. (I
am printing the sql to the browser for testing purposes and the list of
dummy tasks you can see is being generated from the database
recordset).

-------------START
select top 2 ID, DateCreated, DateDue, Priority, Subject, Status from
tasks where Status <> 2 and UserID = 5 order by DateCreated asc

One more task to make it three
Due: Not Specified | Status: Waiting on Someone | Priority: Low
Add more storage to RBS server
Due: Not Specified | Status: Not Started | Priority: Normal
Add tasks list to intranet home screen
Due: 01/10/2006 | Status: In Progress | Priority: High
-------------END

As you can see its ignoring the "top 2" limit and listing all tasks in
the database.
It also does this on my machine in access 2003.

There must be something simple i've got wrong but i can't see it!!

Any help would be appreciated,
Thanks,
Matt C

 

[churchers]

see above...

Ok, after disecting the query a piece at a time (probably should of
done this before my initial post), i've found its the 'order by'
statement thats messing it up.

I can't believe that access doesn't support mixed 'order' and 'select
top' statements.
Most of the time i use limited queries its to get the oldest few of
something, or the newest few of something.

There must be some way of writing my query that causes it to work as
expected

 

[Rick Brandt]

The following is a direct copy and paste from my intranet website. (I
am printing the sql to the browser for testing purposes and the list
of dummy tasks you can see is being generated from the database
recordset).

-------------START
select top 2 ID, DateCreated, DateDue, Priority, Subject, Status from
tasks where Status <> 2 and UserID = 5 order by DateCreated asc

One more task to make it three
Due: Not Specified | Status: Waiting on Someone | Priority: Low
Add more storage to RBS server
Due: Not Specified | Status: Not Started | Priority: Normal
Add tasks list to intranet home screen
Due: 01/10/2006 | Status: In Progress | Priority: High
-------------END

As you can see its ignoring the "top 2" limit and listing all tasks in
the database

The TOP clause includes ties. Include moree fields in the order byu clause
to break the tie. Including the Primary Key shoudl guarantee that.

 

[churchers]

Hi Rick,

Thanks for that
I think i've got it working now