Using Oracle with Access as frontend : column sorting & null values

[delorfra]

I seem to encounter two problems using Access to access Oracle Tables :
1) when I try to sort some tables or queries selecting a specific field, Access won't sort it at all or sort it but with some records left unsorted in the middle of sorted records. Has it to do with indexes or primary keys ..... ?

2) When a field has a specific length, say 10 characters, and is filled with data that I erase using the backspace key in Access, it seems that later on if I use a query with a criteria e.g. &quot;myfield <> 'CANCEL'&quot; it will leave out the records with the field emptied as well (ie not only the records with the undesired value, here 'CANCEL')

Thanks

 

[sem]

As far as I know, Access sorts only those records already fetched. To sort all records, you have to fetch them all (client sort) or change sql statement by adding an appropriate ORDER BY clause (server sort).
As for your second question, comparing to null in most cases returns undefined result, normally evaluated to false in both equality and negation. So null='CANCEL' and null<>'CANCEL' return false. To add rows with null values to your set you may use NVL function or add 'or myfield is null' condition.

 

[delorfra]

Thaks Sem,

OK for the second point that's what I ended up doing : 'myfield <> 'CANCEL' or myfield Is Null'.

As for the sorting, the problem is the same when an 'order by' clause is included in the SQL statement. I think that it happens when tables are joined in a query imposing a sorting by a specific field (maybe indexes or primary keys.. of one the joined tables).

 

[delorfra]

Thanks Sem,

OK for the second point that's what I ended up doing : 'myfield <> 'CANCEL' or myfield Is Null'.

As for the sorting, the problem is the same when an 'order by' clause is included in the SQL statement. I think that it happens when tables are joined in a query imposing a sorting by a specific field (maybe indexes or primary keys.. of one of the joined tables).

 

[sem]

Are the records really unsorted? I had some problems with national characters, displayed similarly to english ones. Users sometimes forget to switch keyboard layout so the data in some fields is displayed correctly while containing wrong characters and also is sorted in quite strange manner.