[AccessD] Sorting textual fields like number fields?

William Hindman wdhindman at bellsouth.net
Tue Dec 2 14:05:45 CST 2003


...I know that, somewhere down deep in hidden recesses, there must be a
simple rationale behind sorting on numbers and THEN street names ...but I'm
not a spelunker by trade ...so I'll just ask? ...inquiring minds just want
to know ...wtfo?

William Hindman
Government is not reason, government is not persuasion,
government is force. It is a dangerous servant." G. Washington

----- Original Message ----- 
From: "Jim Hewson" <JHewson at karta.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Tuesday, December 02, 2003 2:43 PM
Subject: RE: [AccessD] Sorting textual fields like number fields?


>
> He wanted to sort on the street number then street name.
> The other way would sort the street name then the number.
>
>
> -----Original Message-----
> From: Andy Lacey [mailto:andy at minstersystems.co.uk]
> Sent: Tuesday, December 02, 2003 1:13 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [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
> >
> >
>
>
> _______________________________________________
> 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
>




More information about the AccessD mailing list