[AccessD] Sorting textual fields like number fields?

Not the other way round?

Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
> Sent: 02 December 2003 19:03
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [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/a> ccessd
> Website: 
> http://www.databaseadvisors.com 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 
>