custom sort for access table in datagrid

TM

I am using an access database in my vb.net application and it is tied to a
datagrid.

My problem is that the field I want to sort on is a text field, 5 characters
long, and it contains not only numbers but some fields are text.

When I sort the table in access, or use the "order by" sql statement, it
seems to want to put the numbers first, then the alpha after.

I realize this is probably the proper behavior, but is there any way I can
override this ?

The data is for a shopping list and here is a sample:
Field1=Isle
sample data:
1,2,3,4,PHRM,PROD,ENT

I would like to sort the data in this order:
ENT, PROD, PHRM, 1,2,3,...

Sorry for the confusion, this is bard to explain. Is there any way I can do
this ?
Thanks
--
Tony

Nov 20 '05 #1

Cor

Hi TM,

Because you use the SQL select string in your VB.language I would try to
find my solution first
in a special SQL newsgroup and maybe in the active Newsgroup
microsoft.public.dotnet.framework.adonet.

If not you should do extra things with your tables, because I thought also
the dataview has no direct solution for this.

I hope you find the solution for your problem soon.

Cor

"> When I sort the table in access, or use the "order by" sql statement, it

seems to want to put the numbers first, then the alpha after.

I realize this is probably the proper behavior, but is there any way I can
override this ?

Nov 20 '05 #2

TM

The only groups I could find for SQL are SQL server stuff, but I did find
the adonet group and will try there.

I will post back if I can not get a reply. THanks
--
Tony

"Cor" <no*@non.com> wrote in message
news:uy**************@TK2MSFTNGP11.phx.gbl...

Hi TM,

Because you use the SQL select string in your VB.language I would try to
find my solution first
in a special SQL newsgroup and maybe in the active Newsgroup
microsoft.public.dotnet.framework.adonet.

If not you should do extra things with your tables, because I thought also
the dataview has no direct solution for this.

I hope you find the solution for your problem soon.

Cor

"> When I sort the table in access, or use the "order by" sql statement,

it

seems to want to put the numbers first, then the alpha after.

I realize this is probably the proper behavior, but is there any way I can override this ?

Nov 20 '05 #3

Cor

TM,

Is OK I will see if I can help you than.

I have some idea's about it, but it is not nice.

Cor

I will post back if I can not get a reply. THanks
--

Nov 20 '05 #4

TM

I found a custom sort order string in an SQL server web page but can not
figure out how to do this in access or as a command string in my vb.net
application.

The custom search order goes like this:
SELECT *
FROM shoppinglist
ORDER BY
CASE isle
WHEN 'ENT' THEN 1
WHEN 'PROD' THEN 2
WHEN 'PHRM' THEN 3
ELSE 4
END

Any help is greatly appreciated.
--------
Tony
"Cor" <no*@non.com> wrote in message
news:uM**************@TK2MSFTNGP10.phx.gbl...

TM,

Is OK I will see if I can help you than.

I have some idea's about it, but it is not nice.

Cor

I will post back if I can not get a reply. THanks
--

Nov 20 '05 #5

Cor

Hi TM,

Did you try it it in VB.net as a SQL string and so what was the error?
How this goes in Access I don't know at all, I hope you don't ask this for
Access and are thinking that it is in VB.net the same, because that is not
so.

Cor
..

The custom search order goes like this:
SELECT *
FROM shoppinglist
ORDER BY
CASE isle
WHEN 'ENT' THEN 1
WHEN 'PROD' THEN 2
WHEN 'PHRM' THEN 3
ELSE 4
END

Nov 20 '05 #6