Problems with sorting people's names

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

Hi everyone,
Can any of you help advise me on how to overcome sorting
problems for the following?...
The names in a field (called VisitorName) do not conform
to any prescribed format but I'd like to be able to sort
the records by people's surname AND (if possible) by
their first Initial. eg if the following names are in
the field:
A SMITH
G ADAMS
A M WILSON
ANTHONY ADAMS
ANDY HANSON
I'd like them sorted to:
ANTHONY ADAMS
G ADAMS
ANDY HANSON
A SMITH
A M WILSON
I've had a stab at writing some code using the Mid$
function which I had as an Event Procedure within the
Name grouping section of the report (under the 'On
Format' event). This didn't do anything and as I'm
guessing how to write the code I'm frankly not surprised!
Can anyone advise on this? If not, do you know a website
or anywhere that might be able to help?
Below is an extract from my feeble attempt at sorting by
Surname :-(

Dim strName As String

strName = Me.VisitorName

If Mid$(strName, 2, 1) = " " Then

Me.OrderBy = Mid$(strName, 3)

ElseIf Mid$(strName, 3, 1) = " " Then

Me.OrderBy = Mid$(strName, 4)

Blah, blah....

TIA

Lee
 
Thanks for your response Jeff.

How do I get the database to split the names into their
constituent parts though?

Regards,

Lee
 
Lee,
You wrote that your field does not conform to any prescribed format, yet
your sample data all has the last name at the end of the field, i.e.
FirstName or Initial & LastName.

If that is an accurate listing of the way the data is stored, and you have
Access 2000 or later, you can use the InStrRev() function to find the last
space. Everything to the right is last name. Everything to the left is
either first name and/or first (and second) Initial.

[LastNameField] = Mid([FullField],InStrRev([FullField]," ")+1)

[FirstName or Initials] = Left([FullField],InStrRev([FullField]," ")-1)

If you have Access 97 or older, you can write a User defined function to
find the last space and then return the First and LastNames.
If you need help with that, post back.

Then sort the records on LastName, FirstNameInitial

If your data is mixed up with some lastname, firstname records, then you'll
need to first get them all in the same order.
 
Thanks for your suggestions Fred.

Sorry if I've been unclear about the format of the data -
the Surname is always at the end (after a space).
I'm afraid I have Access 97 so would appreciate some help
with creating a function to do this.

Regards,

Lee
-----Original Message-----
Lee,
You wrote that your field does not conform to any prescribed format, yet
your sample data all has the last name at the end of the field, i.e.
FirstName or Initial & LastName.

If that is an accurate listing of the way the data is stored, and you have
Access 2000 or later, you can use the InStrRev() function to find the last
space. Everything to the right is last name. Everything to the left is
either first name and/or first (and second) Initial.

[LastNameField] = Mid([FullField],InStrRev ([FullField]," ")+1)

[FirstName or Initials] = Left([FullField],InStrRev ([FullField]," ")-1)

If you have Access 97 or older, you can write a User defined function to
find the last space and then return the First and LastNames.
If you need help with that, post back.

Then sort the records on LastName, FirstNameInitial

If your data is mixed up with some lastname, firstname records, then you'll
need to first get them all in the same order.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Hi everyone,
Can any of you help advise me on how to overcome sorting
problems for the following?...
The names in a field (called VisitorName) do not conform
to any prescribed format but I'd like to be able to sort
the records by people's surname AND (if possible) by
their first Initial. eg if the following names are in
the field:
A SMITH
G ADAMS
A M WILSON
ANTHONY ADAMS
ANDY HANSON
I'd like them sorted to:
ANTHONY ADAMS
G ADAMS
ANDY HANSON
A SMITH
A M WILSON
I've had a stab at writing some code using the Mid$
function which I had as an Event Procedure within the
Name grouping section of the report (under the 'On
Format' event). This didn't do anything and as I'm
guessing how to write the code I'm frankly not surprised!
Can anyone advise on this? If not, do you know a website
or anywhere that might be able to help?
Below is an extract from my feeble attempt at sorting by
Surname :-(

Dim strName As String

strName = Me.VisitorName

If Mid$(strName, 2, 1) = " " Then

Me.OrderBy = Mid$(strName, 3)

ElseIf Mid$(strName, 3, 1) = " " Then

Me.OrderBy = Mid$(strName, 4)

Blah, blah....

TIA

Lee
Click to expand...


.
Click to expand...
 
Lee,
Place these two functions into a Module.

Public Function ParseLastName(strString as String) as String
' To parse the last name into a new field.
' Assumes names in FirstName or Initials " " LastName order,
' with a space as name separator
' John Smith
' R L Jones
' Lu Ann Adams
' Will return "Smith", "Jones", and "Adams".

Dim intX As Integer
Dim intY As Integer
intX = InStr(strString, " ")
Do While intX <> 0
intY = intX
intX = InStr(intY + 1, strString, " ")
Loop
ParseLastName = Mid(strString, intY + 1)

End Function

===

Public Function ParseFirstName(strString as String) as String
' To parse the First name into a new field.
' Assumes names in FirstName or Initials " " LastName order,
' with a space as name separator
' John Smith
' R L Jones
' Lu Ann Adams
' Will return "John", "R L", and "Lu Ann"

Dim intX As Integer
Dim intY As Integer
intX = InStr(strString, " ")
Do While intX <> 0
intY = intX
intX = InStr(intY + 1, strString, " ")
Loop
If intY = 0 Then
ParseFirstName = ""
Else
ParseFirstName = Left(strString, intY - 1)
End If

End Function

====

Call them from a query:
LastNameField:ParseLastName([FullName])
FirstNameField:ParseFirstName([FullName])

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Lee said:
Thanks for your suggestions Fred.

Sorry if I've been unclear about the format of the data -
the Surname is always at the end (after a space).
I'm afraid I have Access 97 so would appreciate some help
with creating a function to do this.

Regards,

Lee
-----Original Message-----
Lee,
You wrote that your field does not conform to any prescribed format, yet
your sample data all has the last name at the end of the field, i.e.
FirstName or Initial & LastName.

If that is an accurate listing of the way the data is stored, and you have
Access 2000 or later, you can use the InStrRev() function to find the last
space. Everything to the right is last name. Everything to the left is
either first name and/or first (and second) Initial.

[LastNameField] = Mid([FullField],InStrRev ([FullField]," ")+1)

[FirstName or Initials] = Left([FullField],InStrRev ([FullField]," ")-1)

If you have Access 97 or older, you can write a User defined function to
find the last space and then return the First and LastNames.
If you need help with that, post back.

Then sort the records on LastName, FirstNameInitial

If your data is mixed up with some lastname, firstname records, then you'll
need to first get them all in the same order.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Hi everyone,
Can any of you help advise me on how to overcome sorting
problems for the following?...
The names in a field (called VisitorName) do not conform
to any prescribed format but I'd like to be able to sort
the records by people's surname AND (if possible) by
their first Initial. eg if the following names are in
the field:
A SMITH
G ADAMS
A M WILSON
ANTHONY ADAMS
ANDY HANSON
I'd like them sorted to:
ANTHONY ADAMS
G ADAMS
ANDY HANSON
A SMITH
A M WILSON
I've had a stab at writing some code using the Mid$
function which I had as an Event Procedure within the
Name grouping section of the report (under the 'On
Format' event). This didn't do anything and as I'm
guessing how to write the code I'm frankly not surprised!
Can anyone advise on this? If not, do you know a website
or anywhere that might be able to help?
Below is an extract from my feeble attempt at sorting by
Surname :-(

Dim strName As String

strName = Me.VisitorName

If Mid$(strName, 2, 1) = " " Then

Me.OrderBy = Mid$(strName, 3)

ElseIf Mid$(strName, 3, 1) = " " Then

Me.OrderBy = Mid$(strName, 4)

Blah, blah....

TIA

Lee
Click to expand...


.
Click to expand...
Click to expand...
 
Lee

You have suggestions else-thread for how to parse. I would only add that
YOU will have to parse -- if there is no pattern to the names (and John
pointed out that not all names fit one pattern), you will need to USB (use
someone's brain) to determine which part of the field belongs in your new
"surname" field.

Jeff Boyce
<Access MVP>
 
Back
Top