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

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 ?

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 ?
>
>

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
> --

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> > --
>
>

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