[AccessD] Sorting textual fields like number fields?

William Hindman wdhindman at bellsouth.net
Tue Dec 2 16:37:37 CST 2003


...lets see ...I've got an mdb with say 100K addresses and I want only the
one at 114 Main St in Mankato MN ...so I'm going to sort on the 114 and
start looking there first?

...think I missed a turn somewhere :(

...how about a third possibility ...the users are used to working with an
outdated db and don't have a clue what's possible in a modern rdb today
...so instead of telling the analyst what the end result has to be and
letting him figure out how best to provide it, they're telling him how to do
his job.

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

----- Original Message ----- 
From: "Christopher Hawkins" <clh at christopherhawkins.com>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, December 02, 2003 5:10 PM
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
>




More information about the AccessD mailing list