Using InStrRev in ASP to reverse Surname

I refer to my recent question, http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_23138538.html.

I have managed to get my list working in alphabetical order by the surname, this can be seen in action here, http://www.arrivalpreview.co.uk/testing/ee.asp, the top dropdown is the new ordered list and the bottom one is the original list.

I am more than happy with the top list being ordered now by the surname, however there are just a couple of names on there that have a title and this is causing the name to appear incorrectly with the first name still first, i.e. "Bob Smith, Sir" is appearing, but this should be "Smith, Sir Bob".

I understand InStrRev would be able to do this but this will not work in my ASP page as it says it's undefined?

Here is my current query:
SELECT memberID, Right([memberName], len([memberName])-INSTR ([memberName],' ')) + ', '+ Left([memberName], INSTR ([memberName], ' ')) AS reverseName FROM cvs ORDER BY Right([memberName], len([memberName])-INSTR ([memberName],' ')) + ', '+ Left([memberName], INSTR ([memberName], ' '))

Open in new window

djfenomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BadotzCommented:
InStrRev works like InStr, except backwards. I do not believe it will actually swap the order of words (but I have been wrong before ;-)
0
_Stilgar_Commented:
What is undefined?

Anyway, you can try and handle those cases by dropping words like Sir, or looking for them and if they exist take that into account. I think you should either make this as an SP or move the code that does this to your ASP page, since it requires more than simple handling.

Stilgar.
0
djfenomAuthor Commented:
it says InStrRev is undefined?

Can you please advise on how I would do that?

Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

BadotzCommented:
I would say the "undefined" refers to some (missing)(incorrect) parameter in the InStrRev command, or the syntax is wrong...
0
YiogiCommented:
Hi Chris. Ok well I gave it a lot of thought and this is what I came up with. I do hope IIF works in ASP:


SELECT memberID,
IIF(InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ') > 1, 
Right(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), len(Right([memberName], len([memberName]) - InStr(MemberName, ' '))) - InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ')), 
Right([memberName], len([memberName]) - InStr(MemberName, ' ')))
+ ', ' 
+ IIF(InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ') > 1, 
Left([memberName], InStr(MemberName, ' ') + InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ')),
Left([memberName], INSTR ([memberName], ' '))
)
AS reverseName FROM cvs
ORDER BY
IIF(InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ') > 1, 
Right(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), len(Right([memberName], len([memberName]) - InStr(MemberName, ' '))) - InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ')), 
Right([memberName], len([memberName]) - InStr(MemberName, ' ')))
+ ', ' 
+ IIF(InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ') > 1, 
Left([memberName], InStr(MemberName, ' ') + InStr(Right([memberName], len([memberName]) - InStr(MemberName, ' ')), ' ')),
Left([memberName], INSTR ([memberName], ' '))
)

Open in new window

0
djfenomAuthor Commented:
It just says Undefined function 'InStrRev' in expression.
0
YiogiCommented:
I'm not using InStrRev anywhere in the above query?
0
BadotzCommented:
>>I'm not using InStrRev anywhere in the above query?

It must be somewhere else, then. Global search time?
0
_Stilgar_Commented:
@Yiogi - This has nothing to do with ASP so far. Your code is pure SQL.

@djfenom - whats your DBMS?

Stilgar.
0
BadotzCommented:
And I must say, while SQL may be able to do this, you would be better served to do it in code, something along the lines of:

Function Reverse_Name(ByVal strName As String) As String
Dim ra() As String
    ra = Split(strName, " ") ' Isolate name parts
    If UBound(ra) = 2 Then Reverse_Name = ra(1) & ", " & ra(0) ' Last, First
    If UBound(ra) = 3 Then Reverse_Name = ra(2) & ", " & ra(0) & ra(1) ' Last, First Middle
End Function

That way, it is available to you in other code, should you need it, and it uses your SQL for what it was built for: data retrieval.
0
djfenomAuthor Commented:
Sorry Yiogi I was referring to the previous post. I've uploaded my results.

This has now fixed the names with a title, but has made a new problem in that the names ending in QC now start with that. Also there are a couple of names with 2 word surnames, i.e. Roger ter Haar QC is now coming out Haar QC, Roger Ter??
0
BadotzCommented:
>>Also there are a couple of names with 2 word surnames...

You would have a better chance of figuring this out in code, but even then, "Roger ter Haar QC" would be a nasty one to parse correctly...unless you "knew" in advance about that "QC"...
0
YiogiCommented:
@Stilgar - He is using access. I tried to help him in his old post but he had trouble using InStrRev in access queries through ASP pages although it worked in access. This is why I said I hope the IIF will work that way. I know my code works in Access.

@Badotz - He also wants to sort the data. And if its a lot of data it wouldn't be wise to do it in his code. There is a way to do anything but really I ended up building a huge query to do a very simple thing.
0
YiogiCommented:
Chris there is really no way of knowing which names have 2 surnames and which have 2 first names. My code will work with people with 2 first names or a salutation and a first name but yes it won't work with people having 2 surnames. It will also not work with people having 3 first names and a surname or with people having a salutation, 2 first names and a surname.

I would really suggest you use the code you have so far from this post and my previous solution and split your database field in 3 fields. I.E. have a field for first name, one for salutation and 1 for last name. Then it will be quite easy for you to select them, sort them and arrange them in any way you want to.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_Stilgar_Commented:
@djfenom - is it safe to assume one first name? if so, try the following (in ASP code, after you got the name as str):

str = Split(str, " ")
for i = 1 to UBound(str)
   newname = newname & " " & str(i)
next
newname = newname & ", "str(0)

Stilgar.
0
_Stilgar_Commented:
@djfenom - also, if you have a comma (,) in the original string, you could just do:

str = Split(str, ",")
newname = str(1) & ", " & str(0)
0
BadotzCommented:
<rethink>Continuing on without redesigning *something* will resul in premature hair loss</rethink>
0
djfenomAuthor Commented:
Stilgar, where would this go, I've currently got this:

<select name="barrister" id="barrister" onchange="openDir(this.form)">
            <option value="" class="droptop">Please select a member &#8250;</option>
            <%
While (NOT RSmenu.EOF)
%>
            <option value="cvs.asp?id=<%=(RSmenu.Fields.Item("memberID").Value)%>"><%=(RSmenu.Fields.Item("reverseName").Value)%></option>
            <%
  RSmenu.MoveNext()
Wend
If (RSmenu.CursorType > 0) Then
  RSmenu.MoveFirst
Else
  RSmenu.Requery
End If
%>
          </select>
0
_Stilgar_Commented:
<select name="barrister" id="barrister" onchange="openDir(this.form)">
            <option value="" class="droptop">Please select a member &#8250;</option>
            <%
While (NOT RSmenu.EOF)
%>
            <option value="cvs.asp?id=<%=(RSmenu.Fields.Item("memberID").Value)%>"><%=RewriteName(RSmenu.Fields.Item("memberName").Value)%></option>
            <%
  RSmenu.MoveNext()
Wend
If (RSmenu.CursorType > 0) Then
  RSmenu.MoveFirst
Else
  RSmenu.Requery
End If
%>
          </select>


Where RewriteName is:

Function RewriteName(str)
  Dim i, newname
  str = Split(str, " ")
  for i = 1 to UBound(str)
     newname = newname & " " & str(i)
  next
  newname = newname & ", "str(0)
  RewriteName = newname
End Function

Of course you'd need to add some error checking to that function, and change your SQL to read:

SELECT memberID, memberName ....
0
djfenomAuthor Commented:
Ok, I'm now getting "Expected end of statement" on this line:

newname = newname & ", " str(0)
0
_Stilgar_Commented:
newname = newname & ", " & str(0)
0
djfenomAuthor Commented:
Ok, but now I'm getting the same results as I originally had, i.e. "Bob Smith, Sir" is appearing, but this should be "Smith, Sir Bob".

Plus the results are no longer ordered by the Surname.
0
YiogiCommented:
I'll just repeat badotz here:
<rethink>Continuing on without redesigning *something* will resul in premature hair loss</rethink>

It's really better for you to simply redesign the data table and split the columns as I suggested a few posts ago. Otherwise you will keep encountering problems like this every time you want to use that data. And if it takes more time to select the data than doing what you need it for then seriously you have an issue. You will keep wasting time writing extremely complex things to perform simple tasks. Eventually this will end up taking you much more time than it would to redesign once.
0
_Stilgar_Commented:
Try this:

Dim titles
titles = "@@;Sir;Madam;Mr.;Mrs;Miss;"
Function RewriteName(str)
  Dim i, iFirstname, newname, stitle
  str = Split(str, " ")
  iFirstname = 1
  if InStr(titles, str(0)) > 0 then
     iFirstName = 2
     stitle = str(0) & " "
  else
     stitle = ""
  end if
  ' you need to make sure iFirstName < UBound(str) here, if not return original string or whatever
  for i = iFirstName to UBound(str)
     newname = newname & " " & str(i)
  next
  newname = newname & ", " & stitle & str(iFirstname - 1)
  RewriteName = newname
End Function

As for sorting, you could either sort by the same reverseName field you have now, or use this ASP code to add the new strings to an array, sort it, and then spit it out to the response.

Stilgar.
0
_Stilgar_Commented:
And I agree to the redesign comment. The above is not an answer to all the possibilities, hence its not exact and ideal solution.

Stilgar.
0
djfenomAuthor Commented:
Unfortunately redesigning simply isn't an option, I'd have to change literally hundreds of pages just so this one page could be sorted by surname.
0
_Stilgar_Commented:
I say use a stored procedure (a View in Access) using the same mechanics I described above.

Stilgar.
0
swinslowCommented:
SELECT memberID,
(Right(memberName,Len(memberName)-InStrRev(memberName," ")) + ',' + Left(memberName,InStrRev(memberName," "))) AS reverseName
FROM cvs
ORDER BY (Right(memberName,Len(memberName)-InStrRev(memberName," ")) + ',' + Left(memberName,InStrRev(memberName," ")))
0
Anthony PerkinsCommented:
swinslow,

I suspect you did not read the thread, otherwise you would know that the questioner has already tried using InStrRev in JET's SQL dialect.  Unfortuantely, InStrRev is a VBA function and JET only supports a small subset of those function, InStrRev is not one of them.

It pays to test or at least reading the thread before posting.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.