how can get data from table by character order

[shaif]

Hi all, I have a problem with SQL. I using VB with access. the table name T1 and field N as text.

I do the query "select * from T1 order by N"

But my data in the field such as 61UD
72BC
A200

so when i apply this query then i get data : in order of above but I want to sort data by charcter suc as: A200,72BC,61UD.

If any body have any idea please? Thx a lot

[FishVal]

Hello, shaif.

To make your sort working you need to remove leading digits from string.
To achieve this you may advantageously use Val() function which will return leading number in string passed as argument. Then the number may be converted back to string and used in Replace() function.

[code=sql]
SELECT N FROM T1 ORDER BY Replace([N], Trim(Str(Val([N]))), "");
[/code]

Regards,
Fish

[shaif]

Hello, shaif.

To make your sort working you need to remove leading digits from string.
To achieve this you may advantageously use Val() function which will return leading number in string passed as argument. Then the number may be converted back to string and used in Replace() function.

SELECT N FROM T1 ORDER BY Replace([N], Trim(Str(Val([N]))), "");

Regards,
Fish

Hi Fish,
Many thx for your reply , I got a little syntax error that i applied ur sql in VB with access.

Set Rs = New Recordset
 
Rs.Open "select * from Material ORDER BY Replace([N], Trim(Str(Val([N]))),"")", Con, adOpenStatic, adLockOptimistic

I tried diff way to solve it , if it is possible please advise me, again many thx.

[FishVal]

Hello, shaif.

What did you tried so far?
Have you tried to run it in query builder?
If so, then which part of the expression has invalid syntax?

Regards,
Fish

[shaif]

Hello, shaif.

What did you tried so far?
Have you tried to run it in query builder?
If so, then which part of the expression has invalid syntax?

Regards,
Fish

Hi fish,
I tried in VB mostly and get syntax error.

[FishVal]

Hi fish,
I tried in VB mostly and get syntax error.

First, try it mostly on query builder to localize error.

Kind regards,

[shaif]

First, try it mostly on query builder to localize error.

Kind regards,

Hi Fish,
Sorry 4 bother u again. Actually I got stack on the query in Big module, If u can help me out to give some idea please. For ur more understanding I giving u real example that where data I need to sort.

In my table Data : 100 UB * 220* 3, P * 200* 100, X 300* 20, 200 C 100 * 200, D 200 * 900, 600 * 200 J. Its mean all data make by char & number with some special char such as *.

I want to see out put after sorting: 200 C 100* 200, D 200 * 900, 600 * 200 J, P * 200* 100, 100 UB * 220* 3, X 300* 20.

So can u please give me idea abt it. I am actually doing on VB6 and access BD. Many thanks for ur help.

[FishVal]

The idea is rather trivial.

Write a simple public function extracting from a string substring from the first alphabetic symbol found in input string (or this one symbol will be enough - I'm not sure as for logic you mention). Then put a call to this function in query ORDER BY clause like the following.

[code=sql]
SELECT N FROM T1 ORDER BY GetSortCriterio n([N]);
[/code]