Re: Changing Ordering


"Sian" <Sian@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D7CC01E7-6CD8-4DEC-B621-4F2889B1BCFA@xxxxxxxxxxxxxxxx

Not quite sure how to word this.

Is there any way to change the ordering of SQL Server
I need it to order

ABC2 then ABC12 then ABC110 (this is by entire string, rather than
individual characters)

currently it orders it ABC110, ABC12, ABC2

is there anyway we can change this?

If the structure is consistant: 3 caracters followed by n figures I'd sort
on the value of the string minus the first 3 positions. Something like:
Order on VAL(MID([Myfield];4))


Or use another trick to get rid of all but figures '0123456789'


Function NumOnly(strSource) As String
' Get rid of all but figures
' on the fly adapted from other routine and untested

Dim strOK As String
Dim strPart As String * 1
Dim intLus As Integer
Dim intPlaats As Integer

NumOnly = ""
strOK = "0123456789"

While Len(strSource) > 0
strPart = Left(strSource, 1)
strSource = Mid(strSource, 2)
intPlaats = InStr(1, strOK, strPart)
If intPlaats > 0 Then
NumOnly = NumOnly + strPart
End If
Wend

End Function

and go with Order on VAL(NumOnly([MyField])


.

Relevant Pages

  • RE: Delete button dode
    ... Dim strModel As String ... Dim strPart As String ... Dim strNHL As String ...
    (microsoft.public.access.formscoding)
  • RE: Delete button dode
    ... Dim strModel As String ... Dim strPart As String ... Dim strNHL As String ...
    (microsoft.public.access.formscoding)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)