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?
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 GargBusiness Intelligence DeveloperCommented:
Hi,

What result you are getting and what you expect.

Please give sample
0
vbnetcoderAuthor Commented:
i think i am getting them in the order that they where added
0
Dale FyeCommented:
is it a text or memo?

try:

order by  left([textfield], 25)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

order by  left([textfield], 25)
0
Dale FyeCommented:
Just checking to see whether that works or not, since your other method didn't seem to work for you, although it should.
0
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.
0
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
0
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.
0
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
0
Dale FyeCommented:
I would suggest a compact and repair of the database, after making a backup copy.
0
vbnetcoderAuthor Commented:
OK ... strange that that would work because everything else seems to be ok
0
Dale FyeCommented:
When things start acting strange, first order of business is to save a copy and then run C&R.
0
vbnetcoderAuthor Commented:
I've requested that this question be deleted for the following reason:

figured it out by myself
0
Dale FyeCommented:
so, what was the cause of the problem.
0

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.
0
vbnetcoderAuthor Commented:
I ended up ordering by a different field in the table.
0
vbnetcoderAuthor Commented:
ty
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.