RIGHT function, not returning whats expected?

Question

Query:

SELECT StartDate, EndDate, RIGHT(Sector, 1 )
FROM Table1
ORDER BY Right(Sector, 1), StartDate

By looking at this, the query should order everything by sector, followed by the start date. This query has worked for quiet awhile until yesterday where it did not order it properly, for some reason, Sector 2 came before Sector 1.

The data type for Sector is of type int, not null. After inserting a TRIM function into Sector it seems to work fine afterwards.

New Query:

SELECT StartDate, EndDate, RIGHT(Sector, 1 )
FROM Table1
ORDER BY Right(TRIM(Sector), 1), StartDate

Which I found really weird since it's suppose to only pick out one character, so why is there leading spaces?

Is there an issue with using RIGHT function on a int before converting the type? Or is it something else?

Thanks for the help everyone!

-Edit- The RIGHT function should return either 1,2,3 or 4 however when ordering it, 2 comes before 1.

To clarify, the column Sector contains an int value, we can determine it's location by obtaining the last digit (Which is why the previous coder did)

Answer 1

MS Access 2003 has a curious little feature (I can't speak for the other versions):

1. Make a simple query. Sort by Column A Ascending. Save the query.
2. Run the query. When you see the output, sort by Column A Descending using the toolbar option (see pic below). Save & close.
3. Run the query again. Your new sort will have overridden the sort that you saved in the query.

I think you or someone else probably just opened the query out of curiosity, sorted by Sector Descending, and when prompted to save Design Changes, you chose Yes (even though technically you didn't make any). The easiest way I found to restore the original sort is to edit the query and save it.

Answer 2

It seems pretty obvious that you have a blank space at the end of the Sector field that the trim is removing.

Answer 3

You've got your data stored wrong if you need to sort on a subcharacter of a numeric field.

That said, in certain context, VBA functions reserve a space in string representations of numbers for the sign. A nonsensical example of this would be:

  ?Len("12345")
   5 

Notice the space at the beginning (where the - would be if the number returned by Len() could be negative). I thought this was a result of coercing a number to a string value, but that's not it, and I couldn't replicate the problem. But that would likely be the source of the problem, and, of course, trimming off the leading space would take care of the issue.

But that's two function calls for each line, and then you're sorting by it, and that means no use of indexes, so it's going to be slow relative to a SORT BY that can use indexes. So, I'd conclude you have a schema error, in that you're giving meaning to a subpart of the data stored in the field.