Reversing Name Order in Report

[SilentAiche]

Using Access 2002 on Win XP:

I have a single name field capturing full names in F-M-L order (John Doe, James E. Dough, etc.) Some names have middle initials, some do not.

In a report, which is based on a query, I would like to accomplish two things: 1), have the names appear as Dough, Jame E. and 2) have the report in alphabetical order by last name.

In a bound field in the report, I have started with

=Right([onename],Len([onename])-(find(" ",[onename])))

and this is getting no respect at all. When I click print preview, it brings up a dialog box asking me to enter a parameter value for "Find."

I recognize that the above attempt doesn't even address FN or MI yet, but when I am building a complicated formula with commands I seldom use, I like to test it as I go. Here, I wanted the report to show me the last name before I proceeded.

I have seen similar problems posted, but they were usually about splitting "OneName" fields into separate first and last name fields. Neither a forum search nor Help gave me an answer.

As always, your help is appreciated.

Tim

 

[traingamer]

Use Instr() instead of find().

traingamer

 

[traingamer]

or use the split function to build an array and check to see how many entries you have in the array.

Dim names() as string

names = Split([onename], " ")

traingamer

 

[SilentAiche]

Traingamer,

I appreciate the help. It liked Instr better than Find. However, now the first half of my equation is

=Right([onename],Len([onename])-(InStr(" ",[onename])))

Unfortunately, it returns James E. Dough..

I was expecting this to return just the last name before I proceeded trying to get the first name and middle initial so the name on the Report would appear "Dough, James E." I know I must be missing something basic, and I'm really frustrated with Access' Help function right now.

THanks,
Tim

 

[PHV]

If ac2k or above, try the InStrRev function.
But, in my opinion, you have to write your own function to deal with all the inconsistencies you'll discover in the name field ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Unless [onename] has leading spaces, I would have expected it to return "E. Dough" (i.e. everything after the first space)

Try trimming the data first and see if you get a different result.

=Right(Trim([onename]),Len(Trim([onename]))-(InStr(" ",Trim([onename]))))

traingamer

 

[SilentAiche]

Traingamer:

I cut & paste directly from your last post in order to avoid any typos, but it still returned the full name. Quite frankly, I wasn't sure whether to expect it to look at the first space in the name or the second (i.e., whether it would return Dough or E. Dough). Would PHV's alternate function look at the other space?

PHV - InStrRev produced the same results as InStr. And, I tried both in Traingamer's latest... no difference. I'm still getting the whole name.

Tim

 

[traingamer]

uh, duh...
InStr(StringToBeSearched, stringtomatch)

 Right([onename],Len([onename])-(InStr([onename]," ")))

traingamer

 

[traingamer]

Use InStrRev, as PHV suggested and that should get you closer to your answer.

traingamer

 

[SilentAiche]

Traingamer:

Can I take that duh off your hands? I'm pretty sure it's mine.

Seriously, I'm getting excited now. I set up three bound fields to help me develop this formula: one for the last name, one for first name, and one to join them. We've got the first two nailed.

=Right([onename],Len([onename])-(InStr[COLOR=red]Rev[/color]([onename]," ")))

gives me the Last Name correctly, and

=Left([onename],Len([onename])-InStr([onename]," "))

gives me the first First Name and Middle Initial.

However, apparently I am not putting them together correctly:

=Right([onename],Len([onename])-(InStrRev([onename]," ") & ", " & Left([onename],Len([onename])-InStr([onename]," "))))

returns "#Error."

Why?

Tim

 

[SilentAiche]

I actually got the InStrRev fixed before I saw your last post (was kinda proud of it). We must have been writing at the same time.

I'm trying, guys, I'm trying!

Tim

 

[PHV]

=Left([onename],Len([onename])-InStr([onename]," "))
I don't trust the above.
For firstname:
=Left([onename],InStr([onename]," ")-1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Just some parenthesis out of place

Right([onename],Len([onename])-(InStrRev([onename]," "))) & ", " & Left([onename],Len([onename])-InStr([onename]," "))

traingamer

 

[traingamer]

PH is right as always, but to get first name and middle initial (i.e. everything BUT last name), use:

Left([onename],InStrRev([onename]," ")-1)

traingamer

 

[SilentAiche]

Traingamer:

You nailed it - I thought there were a lot of parentheses bunched up at the end.

PHV:

 =Left([onename],InStr([onename]," ")-1)

worked fine except it does not include the middle initial. I thought I was being clever to change it to

 =Left([onename],InStr([onename]," ")+3)

until I quickly realized the first name for people whe didn't have a middle initial would include the first three letters of their last name. James E. Dough was fine (James E.), but Erie Canal wasn't (Erie Can).

Can you tell me more about what you don't like about the first solution (I assume it has to do with data entry putting in too many / not enough spaces).

Deep thanks to you both for your help today. Tomorrow, you'll likely here from me about Part 2 of my original post...

Tim

 

[traingamer]

No, the first solution just happened to work with your example data. Try some data with short first names and long last names and vice versa and you'll see...
The final code shuld be :

Right([onename],Len([onename])-(InStrRev([onename]," "))) & ", " & Left([onename],InStrRev([onename]," ")-1)

traingamer

 

[raskew]

Try this:

Function ParseName(pFullName As String) As String
'**************************************************
'Purpose:   Converts full name (e.g. Elmer T. Fudd)
'           to lname, fname, mname format
'Coded by:  raskew
'Calls:     xLastInStr()
'Inputs:    ? ParseName("Elmer T. Fudd")
'Output:    Fudd, Elmer T.
'Note:      As written, this function will correctly
'           return no / multiple / initial only / no
'           middle name(s) or initials.
'           It will not, however, deal with prefix
'           (e.g. Dr., Rev., etc) or suffix (e.g.
'           Jr., Sr., III, etc.)
'**************************************************

Dim intF As Integer
Dim intL As Integer
Dim strHold As String
Dim strKeep As String

   strHold = pFullName
   intF = InStr(strHold, " ")
   intL = xLastInStr(strHold, " ")
   strKeep = Trim(Mid(strHold, intL)) & ", " & Trim(Left(strHold, intF))
   strKeep = strKeep & IIf(intF <> intL, RTrim(Mid(strHold, intF, intL - intF)), "")
   ParseName = strKeep
   
End Function

Function xLastInStr(pStr As String, _
                    pDelim As String) As Integer
'**************************************************
'Purpose:   'Roll-your-own' A97 replacement for the
'           InStrRev() function found in A2000 and
'           later.
'Coded by:  raskew
'Inputs:    ? xLastInStr("the quick brown fox", " ")
'Output:    16
'**************************************************

Dim i    As Integer
Dim n    As Integer
Dim tlen As Integer

    n = 0
    tlen = Len(pDelim)
    For i = Len(RTrim(pStr)) To 1 Step -1
    
      If Mid(pStr, i, tlen) = pDelim Then
          n = i
          Exit For
      End If
    Next i
    
    xLastInStr = n

End Function

HTH - Bob

 

[SilentAiche]

Traingamer & PHV:

It "works a treat," as some of my new favourite people might say. T, you were right about the long and short names not working properly, but with your last answer everything appears okay. You were right about PH, too.

raskew: Bob, I really appreciate the effort with all that code, but I'm not sure how or whether to use it at this point. As you can see from the last few posts, we (ha!) seem to have found a solution. How would your function work in conjunction with the expression that was developed? Does your function have advantages?

You noted that the function will not deal with prefixes or suffixes. The new expression deals well with prefixes but not with suffixes, so in that small regard it may have an advantage. I think I won't worry about the suffixes- I'll delete them from the data.

Additional comments are welcome. I think I'll play with the alphabetizing question now.

Also, please tell me that I didn't really say in my last post yesterday that you would here from me today. Good grief.

Thanks guys,
Tim

 

[traingamer]

There is nothing to the second part of your problem. Use the above as an expression in your query and sort on that.

'nuff said

traingamer

 

[SilentAiche]

It appears we have complete success after a couple of hours of hair pulling. When I first replaced "onename" with the actual field name I got #Error. Did I mention I was not the original designer of this database? The actual field name had six words (separated, naturally, by five spaces). Even I know better than that. So I changed the field name to a one-word name and it eventually worked, although the first report I tried to upgrade was impossible. I recreated the report from scratch and everything worked, but nothing I did worked on the original report. Strange. Fortunately, all the other reports this effected were repairable in their original form.

Thank you again for all you've taught me. I look forward to using the knowledge of other databases that I have inherited (because if I designed it, every element of a name has its own #$%@& field!).

Tim