[AccessD] Sorting textual fields like number fields?

This does the same thing
ORDER BY Val([Address]), Mid([Address],InStr([Address]," "))

Jim

-----Original Message-----
From: Christopher Hawkins [mailto:clh at christopherhawkins.com]
Sent: Tuesday, December 02, 2003 1:01 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Sorting textual fields like number fields?


Never mind; I figured it out.

I found some code that almost did what I needed and changed it to
look like this:

***START***
Function FilterTextOutOfAddress(strProblemAddress As String) As Double

Dim strResults As String
Dim iCounter As Integer
Dim iStartHere As Integer

On Error GoTo Err_FilterTextOutOfAddress

    ' Set the starting position.
    iStartHere = 1

    ' Loop through the length of the address
    For iCounter = 1 To Len(strProblemAddress)
        ' If the current character is a number, add it to the result
string.
        ' If not, don't.
        If IsNumeric(Mid(strProblemAddress, iStartHere, 1)) Then _
            strResults = strResults & Mid(strProblemAddress,
iStartHere, 1)
   
            ' Advance one character.
            iStartHere = iStartHere + 1
    Next
    
    ' Return the result string in numeric format.
    FilterTextOutOfAddress = CDbl(Trim(strResults))

Exit_FilterTextOutOfAddress:
    Exit Function

Err_FilterTextOutOfAddress:
    MsgBox Err.Description
    Resume Exit_FilterTextOutOfAddress
End Function

***END***

Then I added a field to my query that looks like this:

StreetNum: FilterTextOutOfAddress([Address])

And sorted it ascending, followed by the actual Address field, also
sorted Ascending so that the sort will go number first, then street
name.

-Christopher-
---- Original Message ----
From: clh at christopherhawkins.com
To: accessd at databaseadvisors.com, 
Subject: RE: [AccessD] Sorting textual fields like number fields?
Date: Tue, 2 Dec 2003 11:28:02 -0700

>All,
>
>I feel like I should already know how to do this, but for whatever
>reason - I don't.  Must be the pneumonia clouding my thinking. 
>Here's the problem:
>
>I need to sort an address field by street number.  The Address
>(street number and street name) is contained in a Text field, so my
>attempts to sort it end up with the numbers being sorted like text.
>For example, given addresses 1118 Main St., 1144 Maple St., 1146 Oak
>St., 113 East St. and 115 West St. the sort would look like this:
>
>1118 Main St.
>113 East St.
>1144 Maple St.
>1146 Oak St.
>115 West St.
>
>I need the sort to go exactly by street number, like this:
>
>113 East St.
>115 West St.
>1118 Main St.
>1144 Maple St.
>1146 Oak St.
>
>And I don't have a clue how to begin.  Heeeeeeeeeeeeeeeeeeeeeelp...
>
>-Christopher-
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com