Sorter data

jeg mener det er
ORDER BY LEN(dit-felt), dit-felt

If I understand your problem correctly you want to sort by the NUMBER part only and NOT the text!

If this is correct then you have to EXTRACT the number part of the field and sort by this. If there is a specific format for the field then thats not  aproblem but if it can vary then it is.
Example:

DEN 1256
GB 234
HOL 9
here there isnt MUCH to go by but if it was a FIXED format then it is.
Hope you understand the problem!

Terry>Dit eksempel vil kunne forekomme. Betyder det, at det ikke kan lade sig gøre?

alt (næsten) kan lade sig gøre!
but it just isnt always so easy to solve!
I am right in saying that you want to sort by the number part?

Will the number part ALWAYS come at the end or is it possible to be for example

ABC 123 DEF

The problem is how many possibilities there are!

Nej, nummeret vil altid komme til sidst.

Ok that makes it easier!
I think it will be necessary to make a function which finds the first numeric character and takes the remainder of the field and sort by that. If I make a function can you implement this in your query?

Og ja, der skal kun sorteres på nummeret. Nationalitetsbetegnelsen har ingen relevans i den her forbindelse.

Jeg paster lige scriptet, så du kan se det. Øjeblik (det ligger på en anden pc)

<%





' ADODB connection objektet
Set Conn = Server.CreateObject("ADODB.Connection")
' Husk at angive den rigtige sti til din database
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("/bilerpaavestfyn/riishojwebsoft.mdb")
' Åben databaseforbindelsen
Conn.Open DSN

' SQL forespørgsel
strSQL = "Select * From optiA Order by sejlnummer"
Set rs = Conn.Execute(strSQL)
If Not (rs.BOF Or rs.EOF) Then
    Response.Write "<h4 align=center><font face=Verdana>Følgende er tilmeldt Optimist A</font></h4><br>"
    Response.Write "<h4 align=center><font face=Verdana>These are the entries of Optimist A</font></h4><br>"

    Response.Write "<table border=0 cellpadding=3>"
   
    Response.Write "<tr bgcolor=#336699><th ><font color=white face=verdana size=2>Sejlnummer</font></th><th><font color=white face=verdana size=2>Navn</font></th><th><font color=white face=verdana size=2>Sejlklub</th></tr>"
  Do While Not rs.EOF
 
        Response.Write "<tr bgcolor=#000066><td><font color=white face=verdana size=2>" & rs("sejlnummer") & "</font></td>"
        Response.Write "<td><font color=white face=verdana size=2>" & rs("navn") & "</td>"
        Response.Write "<td align=center><font color=white face=verdana size=2>" & rs("sejlklub") & "</td>"
       

       
       
       


        rs.MoveNext
   
    Loop
End If

' Rydder op efter os
Conn.Close
Set Conn = Nothing
%>

Here is the function, copy it to a module and use it like this in the query

SELECT *
FROM YourTable
ORDER BY GetNumPart([TheField]);

Hehe :o) No mention of ASP in the question!

Anyway this can still be used but you have to make a SELECT on the query and NOT the table

And the function
Function GetNumPart(part As String) As Long
Dim i As Integer

    getNumber = 0 'Just to make sure we dont fall off the end
    For i = 1 To Len(part)
   
        If IsNumeric(Mid(part, i, 1)) Then
       
            GetNumPart = Mid(part, i, Len(part) - (i - 1))
            Exit For
       
        End If
       
       
    Next i

   
End Function

Once you have made a query you just need to change this line

strSQL = "Select * From optiA Order by sejlnummer"
to
strSQL = "Select * From qryoptiA"

where qryoptiaA is the name of the query

Jeg er ikke sikker på, at jeg helt forstår det. Kan du evt. paste det du har lavet ind i mit script ovenover?

riishoj>This CAN NOT be pasted into your ASP script it HAS to be done in the database.

NOTE: There was NO mention of ASP in the original question!

ok. Jeg prøver at flytte spørgsmålet til asp. Anyway, tak for hjælpen.

selv tak:
riishoh>I DO NOT think you can do this from ASP!

What is wrong with doing it in the Access database as I suggest?

Jeg ved ikke hvordan man laver det i databasen. Derfor vil jeg prøve asp!