sorting text field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What format can i use in order to sort one field containing first and last
name by last name only? In table design view i have tried to put in a format
that would allow me to sort in the report design view, but i keep getting
error messages.
Any help would be appreciated.
 
There's no reliable way to sort under those conditions.

In what order would you want the following names sorted?

Mary Lou Retton
Ludwig von Beethoven
Walter de la Mare
Bono
 
A couple of points to consider.
As Douglas points out, the sorting would not be reliable.
I would recommend not sorting tables, but perform the sorting in you
queries, reports, or forms.
I would also recommend you not carry first and last name in the same field.
A frequent question is "how do I parse out my first and last name from my
name field?" The answer is, you can't do this with 100% accuracy. There are
two many variable possibilites. The same holds true for addresses. It will
make your life much easier if you carry each part of a name separately. To
put them together for display is very easy. For a complete name you might
consider:
Prefix (Mr. Mrs., Dr. Sir, etc)
First Name
Middle Name
Last Name
Suffix (Esq, Jr, IV, etc)
 
Many thanks for your reply, this is an assignment request, can you tell me if
there is an unreliable way? Hypothetically speaking if all the names were
simple like Jack Russell.
 
First, drop the class. The instructor is an idiot :)
I don't believe it can be done at the table level.
You can do it with a query. To do that, you will need to create a
calculated field in the query and parse out the name. The following example
assumes the rightmost part of the name field will always be the last name:

SortBy: Right([employeename],Len([employeename])-InStrRev([employeename]," "))
 
What format can i use in order to sort one field containing first and last
name by last name only? In table design view i have tried to put in a format
that would allow me to sort in the report design view, but i keep getting
error messages.
Any help would be appreciated.
Click to expand...

I fully agree with Douglas and Dave about the difficulties with this. I *hope*
the instructor is just giving an object lesson in why names should be broken
down into components as Dave suggests!

That said... you can base the Report on a Query containing two calculated
fields:

FirstName: Left([name], InStr([name], " "))
LastName: Trim(Mid([name], InStr([name], " ")))

Use these fields in the Report's Sorting and Grouping dialog; that's the only
way to sort data in a Report, it will ignore the sort order of a Query and
there *is* no such thing as the sort order for a table.

John W. Vinson [MVP]
 
Bea. said:
Many thanks for your reply, this is an assignment request, can you tell me if
there is an unreliable way? Hypothetically speaking if all the names were
simple like Jack Russell.
Click to expand...

Maybe it's a trick question and the correct answer is you can't:-)

gls858
 
Thanks for your suggestion.

John W. Vinson said:
What format can i use in order to sort one field containing first and last
name by last name only? In table design view i have tried to put in a format
that would allow me to sort in the report design view, but i keep getting
error messages.
Any help would be appreciated.
Click to expand...

I fully agree with Douglas and Dave about the difficulties with this. I *hope*
the instructor is just giving an object lesson in why names should be broken
down into components as Dave suggests!

That said... you can base the Report on a Query containing two calculated
fields:

FirstName: Left([name], InStr([name], " "))
LastName: Trim(Mid([name], InStr([name], " ")))

Use these fields in the Report's Sorting and Grouping dialog; that's the only
way to sort data in a Report, it will ignore the sort order of a Query and
there *is* no such thing as the sort order for a table.

John W. Vinson [MVP]
Click to expand...
 
Thankyou.

Klatuu said:
First, drop the class. The instructor is an idiot :)
I don't believe it can be done at the table level.
You can do it with a query. To do that, you will need to create a
calculated field in the query and parse out the name. The following example
assumes the rightmost part of the name field will always be the last name:

SortBy: Right([employeename],Len([employeename])-InStrRev([employeename]," "))

--
Dave Hargis, Microsoft Access MVP


Bea. said:
Many thanks for your reply, this is an assignment request, can you tell me if
there is an unreliable way? Hypothetically speaking if all the names were
simple like Jack Russell.
Click to expand...
Click to expand...
 
Back
Top