Filterin a SQL Table (1 Viewer)

[Jaspal]

Hi all,

I have a SQL table linked in to an access frontend, sometimes the table needs to be opened from the table view in access and filtered upon. The table has a primary key which is an autonumber, however when filtering on some data in the table the results return as if the table has lost its indexing, and the autonumber (primary Key) is all jumbled, and requires manually re sorting

Can anyone help as to why this happens or how to stop it from happening

Thanks in advance

 

[pdx_man]

I don't think I understand fully. So you have a table that looks like

A_Field Color
1 Brown
2 Red
3 Blue
4 Green
5 Burnt Orange

And you filter with Like 'b*' on Color and it returns something like

5 Burnt Orange
3 Blue
1 Brown

Is that right?

 

[Jaspal]

Yes thats right, surely it should be returning those results in numerical order as my ID field is a primary key (also an auto number)

 

[pdx_man]

I have seen this before in Access. It does ignore the index when doing filters as it is dynamically adding a WHERE clause to the recordset. You will have to manually sort the table after filtering.