Perry Harold
pharold at proftesting.com
Tue Dec 2 16:26:39 CST 2003
It's because the numbers come first in the ASCII chart of values. Perry Harold -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Christopher Hawkins Sent: Tuesday, December 02, 2003 5:10 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Sorting textual fields like number fields? William, Two possibilities: 1) People are too lazy to read past the first element of the address unless absolutely necessary. 2) People are too smart too waste energy looking past the first elemtn of the address unless absolutely necessary. I'll give you three guesses as to which one I think it true... -Christopher- ---- Original Message ---- From: wdhindman at bellsouth.net To: accessd at databaseadvisors.com, Subject: Re: [AccessD] Sorting textual fields like number fields? Date: Tue, 2 Dec 2003 15:05:45 -0500 >...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 >> > > >_______________________________________________ >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