reverse/mirror sorting

[camaE]

i have a lists of emails+domain names eg camae@yahoo.com. im trying to sort them in reverse order but "reverse" seems not working in access. anyone knows how to do them or i was thinking of sorting the list begin with the domain names (which is after the '@') but i dont know how. ive tried using "like "*@[a-d]" but the result displayed is not sorted in alphabetic order. sorry im a beginner. thanks in advance

 

[RuralGuy]

Use the Query Builder to experiment. It is your friend.

 

[john471]

Try this on for size...

SQL:-

SELECT Right([emailAddress],Len([emailAddress])-(InStr(1,[emailAddress],"@"))) AS DomainPart, Left([emailAddress],InStr(1,[emailAddress],"@")-1) AS NamePart
FROM tblYourTableName
ORDER BY Right([emailAddress],Len([emailAddress])-(InStr(1,[emailAddress],"@"))), Left([emailAddress],InStr(1,[emailAddress],"@")-1);

 

[camaE]

Thanks John471...ill try that.

 

[camaE]

im sorry but john, i dont understand. lets say i have this list on my Table.Field1

freddy@howl.com
corn@erols.com
arith@fry.net
burn@aol.com

and i want it to be sorted as this which is the char after '@' is picked for sorting

burn@aol.com
corn@erols.com
arith@fry.net
freddy@howl.com

and not

arith@fry.net
burn@aol.com
corn@erols.com
freddy@howl.com

..so the code u gave..in the [emailAdress] i should pick one of those addresses? thanks.

 

[raskew]

Hi -

SELECT tblTest9.MyField
FROM tblTest9
ORDER BY ySplit([myfield],"@",False);

....with ySplit (roll-your own Split() function written in A97 but usable in later versions)

Function ySplit(ByVal pTarget As String, pItem As String, _
         Optional ShowLeft As Boolean = True) As String

'*******************************************
'Purpose:   Splits a string to the left or
'           right of pItem
'Coded by:  raskew
'Inputs:    1)  ySplit("The quick+ brown fox", "+", True)
'           2)  ysplit("The quick+ brown fox", "+", False)
'Output:    1)  The quick
'           2)  brown fox
'*******************************************

Dim strLeft  As String
Dim strRight As String
Dim n        As Integer

    n = InStr(pTarget, pItem)
    If n = 0 Then
       ySplit = ""
    Else
       ShowLeft = IIf(IsMissing(ShowLeft), True, ShowLeft)
       strLeft = Left(pTarget, n - 1)
       strRight = Mid(pTarget, n + 1)
       ySplit = Trim(IIf(ShowLeft, strLeft, strRight))
    End If
    
End Function

HTH - Bob

 

[camaE]

ermm..im completely lost..again, im a beginner in access and that code u gave me seems like a vb code to me (i've learn vb thou') are you suggesting me to use vb form to do the query? btw im using access 2k. thanks bob

 

[raskew]

Copy / paste the function into a new module.

Copy / paste the query-sql into a new query, changing the table and field name to your own.

Run the query. It should provide what you're after.

HTH - Bob

 

[camaE]

thanks reskew. its works. thanks again