Sorting in table field

[ypma]

Hello fellow users. I require little assistance . I have a table which has a field called Last. I have tried to sort this column , but there are a number of records which will not sort . If I enter the details of record that wont sort into a new record, this record is sorted.

Any pointer would be appreciated . I have tried compact and repair .

Regards Ypma

 

[Ranman256]

this makes no sense. ALL records can sort.
now, if you enter numbers in a text field, they wont sort correctly.

 

[plog]

All those incorrectly sorting fields begin with a tab character, not the letter you think they do. You can verify this by selecting one of those records and pasting it into an Excel cell--it will actually put the Last Name value in the next cell, not the one you intend to paste it into.

To fix this run this query to remove that tab character from all that have it:

UPDATE Table1 SET Table1.[Last] = Mid([Last],IIf(Asc(Mid([Last],1))=9,2,1));

Of course, be sure to backup the table before running that query, just in case.

 

[Galaxiom]

I have a table which has a field called Last.

Last is a function name and should be avoided for any object name.

 

[The_Doc_Man]

Or use the TRIM function which should remove leading/trailing spaces and tabs.

 

[ypma]

Plog. Thank you very much the update query , it did the trick. I had to use it on some other fields too. It begs the question how can this happen? the records concerned were all manually inputted by different users not all were effected .

Regards Ypma

 

[ypma]

Docman. thank you for your suggestion. I was not sure how to apply Trim so I used the update query that Plog provided.

Regards Ypma