SQL order by

I have a short text field and want to order by alphabetical

So i do this:

Select TextField
From Table
Order by TextField

i expected the result to be alphabetic but that's not what i am getting.
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hi,

What result you are getting and what you expect.

Please give sample
vbnetcoderAuthor Commented:
i think i am getting them in the order that they where added
Dale FyeOwner, Developing Solutions LLCCommented:
is it a text or memo?

try:

order by  left([textfield], 25)
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

vbnetcoderAuthor Commented:
it is short text
vbnetcoderAuthor Commented:
curious why are u doing it this way?

order by  left([textfield], 25)
Dale FyeOwner, Developing Solutions LLCCommented:
Just checking to see whether that works or not, since your other method didn't seem to work for you, although it should.
PatHartmanCommented:
Is your field a table level lookup by chance?  If so, it is sorting on the ID of the value rather than the text string.

The best solution would be to remove ALL table level lookups.  Then whenever you need both the ID and the text string in a query, you would need to join to the lookup table to retrieve the text value.
vbnetcoderAuthor Commented:
it not a lookup ... its just a table to put items in a dropdown.


The items are something like this ... letters followed by

bbbb- sdfs
xxxxxxxx - sksdkkd
abc - ddd
PatHartmanCommented:
In what context do you expect the data to be sorted?  

Are you saying that you are using this table as the RowSource for a combo?  In that case you need to specify the sort property or use a query with an order by clause as the RowSource rather than the table.

Are you opening the table in datasheet view?  Unless you specifically sort the table, it won't be in a "defined" sequence.  In practice, this aspect of Access fools people into thinking that the ds view is explicitly sorted because it always seems to be in PK order.  Don't be fooled.  Queries (which this is) are unordered sets and if you require some specific order, you MUST specify a sort order.

Are you opening a form bound to the table, same explanation as opening in ds view.
vbnetcoderAuthor Commented:
or use a query with an order by clause as the RowSource rather than the table.  << this is what i am doing.  I order by and it is not ordering alphabetically

If i run a query on the table ordering by the field i want to order it does not sort either
Dale FyeOwner, Developing Solutions LLCCommented:
I would suggest a compact and repair of the database, after making a backup copy.
vbnetcoderAuthor Commented:
OK ... strange that that would work because everything else seems to be ok
Dale FyeOwner, Developing Solutions LLCCommented:
When things start acting strange, first order of business is to save a copy and then run C&R.
vbnetcoderAuthor Commented:
I've requested that this question be deleted for the following reason:

figured it out by myself
Dale FyeOwner, Developing Solutions LLCCommented:
so, what was the cause of the problem.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
It looks like Dale solved the problem.  If not, we'd like to know what the solution was rather than delete the question.
vbnetcoderAuthor Commented:
I ended up ordering by a different field in the table.
vbnetcoderAuthor Commented:
ty
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.