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 >