String Functions and Queries

[warpathmechanic]

Hello,

I have used Hyperion (Brio) to query a databse for several pieces of information. After exporting those to a .csv format and importing them to an access 2007 table I have the following issue:

The "Owner" field is formatted as

Last/First/Middleinitial

Yes this includes the forward slashes. I have written this string so far:

Expr3: Mid([Owner],InStr([Owner],"/")+1) & " " & Left([Owner],InStr([Owner],"/")-1)

Which gets me something that looks like this

John/R Patton

What do I need to add to this string to make it so it appear like this?:

John R. Patton

 

[GinaWhipp]

I would have used the Replace() funtion to remove the forward slashes and then an Update query to sat if MiddleInitial is not longer then 1 and not null put a period...

Replace("YourFeild","/"," ")

...then

UPDATE YourTable SET YourTable.YourField = [Yourtable]![YourField]+"."
WHERE (((Len([YourField]))=1 And Not (Len([YourField])) Is Null));

PLEASE make a back-up first for the oops factor!

 

[warpathmechanic]

I would have used the Replace() funtion to remove the forward slashes and then an Update query to sat if MiddleInitial is not longer then 1 and not null put a period...

Replace("YourFeild","/"," ")

...then

UPDATE YourTable SET YourTable.YourField = [Yourtable]![YourField]+"."
WHERE (((Len([YourField]))=1 And Not (Len([YourField])) Is Null));

PLEASE make a back-up first for the oops factor!

The period is really trivial. Sorry Im new to Access's query arguments. So once I replace the "/", how would I then rearrange the text? Wouldn't having the "/" as a reference point for Left, Right, Mid be easier?

 

[plog]

This will do it without any updating of your data:

ParsedName: Replace(Mid([Owner],InStr([Owner],"/")+1),"/"," ") & ". " & Mid([Owner],1,InStr([Owner],"/")-1)

 

[GinaWhipp]

Ummm, I thought you had the rearranging figured out by your first post and the left you with one forward slash...

 

[warpathmechanic]

This will do it without any updating of your data:

ParsedName: Replace(Mid([Owner],InStr([Owner],"/")+1),"/"," ") & ". " & Mid([Owner],1,InStr([Owner],"/")-1)

That worked GREAT!. My only question is that my supervisor has informed me that there may be a set where 3 "/" are involved for Suffixes:

Last/First/Middle/Suffix
Smith/John/B/Jr

I have this:

First Name: IIf(InStr(InStr(1,[Table1]![Field1],"/",1)+1,[Table1]![Field1],"/",1)>0,Mid([Table1]![Field1],InStr(1,[Table1]![Field1],"/",1)+1,InStr(InStr(1,[Table1]![Field1],"/",1)+1,[Table1]![Field1],"/",1)-InStr(1,[Table1]![Field1],"/",1)-1),Mid([Table1]![Field1],InStr(1,[Table1]![Field1],"/",1)+1,Len([Table1]![Field1])-InStr(1,[Table1]![Field1],"/",1)))

but there HAS to be an easier way to do it.

 

[plog]

At this point I'd go with a custom VBA function:

Function ParseName(inputname)
    ' Takes a slash delimited name and parses it (i.e. Smith/John/R/Sr becomes John R. Smith Sr)
Dim f, m, l, s As String
    ' variables to hold individual parts of name
l = Mid(inputname, 1, InStr(inputname, "/") - 1)
inputname = Mid(inputname, InStr(inputname, "/") + 1)
    ' extracts last name and gets input string ready to extract first
f = Mid(inputname, 1, InStr(inputname, "/") - 1)
inputname = Mid(inputname, InStr(inputname, "/") + 1)
    ' extracts first name and gets input string ready to extract middle initial
If (InStr(inputname, "/") > 0) Then
    ' if a suffix is in the data it will extract it, else it uses whats left as middle initial
    m = Mid(inputname, 1, InStr(inputname, "/") - 1)
    s = " " & Mid(inputname, InStr(inputname, "/") + 1)
Else: m = inputname
End If
ParseName = f & " " & m & ". " & l & s
    ' compiles parts of name into the name to display and returns it
End Function

Create a new module and paste the above code into it. Then to use it in a query make a field that looks like this:

ParsedName: ParseName([Owner])

 

[jdraw]

Why don't you structure your table appropriate to the usage you are describing?
You could add fields to your table (breaking the combination of names initial and suffix into separate fields), or you could make a new table and just keep the individual fields.

Table structure is the root of your issue, in my view.

 

[warpathmechanic]

Why don't you structure your table appropriate to the usage you are describing?
You could add fields to your table (breaking the combination of names initial and suffix into separate fields), or you could make a new table and just keep the individual fields.

Table structure is the root of your issue, in my view.

I would but unfortunately the information is coming from an old AS400 table and unless I do a ton of manual work on a daily basis, it turns into a nightmare.

As far as the VBA function, is there a way to do it without it by adding another line to your original code Plog? Or at this point does it simply get way too complex?

 

[jdraw]

Here's a function that will return parts of the name from a field. You can select first, last or initial. You could call this multiple times to get all pieces required.

'---------------------------------------------------------------------------------------
' Procedure : ParseName
' Author : Jack
' Created : 2/23/2010
' Purpose : To parse a field containing the person's full name and to return
' the first name, or the initial if it exists, or last name depending on the
' value of strWhich.
'
' NOTE: The format of the fullname field is
' Lastname, Firstname Initial(may not be present)
' eg a)De Jesus, Charlene K.
' b)O'Sullivan, Margaret
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: strname == the person's fullname
' strWhich = F First Name
' = M Middle Initial
' = L Last Name
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function ParseName(strName As String, strWhich As String) As String


Dim strUtil As String
Dim strLastname As String
Dim strFirstname As String
Dim strMiddle As String
On Error GoTo ParseName_Error

strUtil = Trim(strName)
strLastname = Left(strUtil, InStr(1, strUtil, ",") - 1)
strMiddle = Mid(strUtil, InStrRev(strUtil, " ") + 1)
If Len(strMiddle) <> 1 Then
strMiddle = vbNullString
Else
ParseName = strMiddle
strUtil = Mid(strUtil, 1, Len(strUtil) - 2)
End If
strFirstname = LTrim(Mid(strUtil, InStr(1, strUtil, ",") + 1))

Select Case strWhich
Case "F"
ParseName = strFirstname
Case "L"
ParseName = strLastname
Case "M"
ParseName = strMiddle
Case Else
ParseName = vbNullString
End Select


On Error GoTo 0
Exit Function

ParseName_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ParseName of Module Module4"

End Function

Also as far as the work effort to adjust the AS400 data, it depends on how many other queries etc involving the name parts. If you have a lot of querying and reporting etc involving firstname, lastname.. then adjusting the table by means of a converter otine would be a one time effort. However, if you don't have any volume of work requiring individual fields then, a function or series of function calls may be most appropriate.

Good luck.

 

[warpathmechanic]

The VBA help is amazing but unfortunately I dont know the first thing about it. Where would I put it and how would I test to make sure it works?

 

[jdraw]

Do you have a test table with some sample names?
Let's consider a test table called TestNames with
field Fullname
and let's put in some test records

Smits,Jonathan P.
Payne,Ima
del la Hoya,Oscar
O'Brien,Megan M.
Van't Goor,Ali
De Jesus,Sheilaugh
Del Carrico,Efriam O.
O'Sullivan,Sean
D'Ata Orallia,Charlene K.
Del a'grande,Antonio W.
Doe,John

Then, set up a test procedure

Sub jnames()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TestNames")
Do While Not rs.EOF
Debug.Print rs!FullName & " -**- " & ParseName(rs!FullName, "F") & ParseName(rs!FullName, "M") & " " & Trim(ParseName(rs!FullName, "L"))
rs.MoveNext
Loop
End Sub

And run the test procedure. You will see the output in the immediate window.

 

[Brianwarnock]

The VBA help is amazing but unfortunately I dont know the first thing about it. Where would I put it and how would I test to make sure it works?

Plog told you at the end of his post.

Brian

 

[warpathmechanic]

Plog told you at the end of his post.

Brian

When I tried that, it always gives me invalid reference ParseName in Query

 

[Brianwarnock]

You didn't call the module parsename did you? The module name must not be the same as any subs or functions in it.

Brian

 

[warpathmechanic]

You didn't call the module parsename did you? The module name must not be the same as any subs or functions in it.

Brian

That fixed it, however now I am getting a Runtime: 94 Error Invalid use of Null regarding this line:

inputname = Mid(inputname, InStr(inputname, "/") + 1)

 

[Brianwarnock]

What that line is doing is moving througth yje inputname field, however there is no check that there is more data, the code needs a rethink. It needs to be able to cope with any number of parts as no doubt Dr Fred Alan John Smith Jnr will appear one day, just after Billy Smith.

Brian

 

[plog]

My guess is that all of your data isn't formatted like you said it was. likely there is data in there with just 1 slash (i.e. John/Smith) or possibly no slashes. My code was tailored to your description which is why it is breaking.

If you look at my code you will see it essentially breaks off a sub-string based on where the next slash appears, places it in the appropriate variable and then updates the initial string to exclude that data just broken off as well as the slash it used. It repeats this until it is out of slashes.

If you don't know how many slashes are in your data then the code will need to be reworked to include more of this line:

If (InStr(inputname, "/") > 0) Then ...

Before lines that have this code in it:

... Mid(inputname, 1, InStr(inputname, "/") - 1)


Think of your initial string as a Hershey Chocolate bar, the data you want out as individual pieces of chocolate and the slashes as those little gaps between the pieces of chocolate so that you know where to break the pieces.

Before you attempt to break off a piece you must first verify there are at least 2 pieces left by checking to see if a delimter is in there. If so, break off the piece up to that delimiter and place it in the appropriate pile. If not, then what you have is the final piece which doesn't need to be broken, it just needs to be place in the appropriate pile.

 

[warpathmechanic]

My guess is that all of your data isn't formatted like you said it was. likely there is data in there with just 1 slash (i.e. John/Smith) or possibly no slashes. My code was tailored to your description which is why it is breaking.

If you look at my code you will see it essentially breaks off a sub-string based on where the next slash appears, places it in the appropriate variable and then updates the initial string to exclude that data just broken off as well as the slash it used. It repeats this until it is out of slashes.

If you don't know how many slashes are in your data then the code will need to be reworked to include more of this line:

If (InStr(inputname, "/") > 0) Then ...

Before lines that have this code in it:

... Mid(inputname, 1, InStr(inputname, "/") - 1)


Think of your initial string as a Hershey Chocolate bar, the data you want out as individual pieces of chocolate and the slashes as those little gaps between the pieces of chocolate so that you know where to break the pieces.

Before you attempt to break off a piece you must first verify there are at least 2 pieces left by checking to see if a delimter is in there. If so, break off the piece up to that delimiter and place it in the appropriate pile. If not, then what you have is the final piece which doesn't need to be broken, it just needs to be place in the appropriate pile.

I added the appropriate IF statements and End Ifs and it works like a well greased engine. THANK YOU SO MUCH FOR YOUR HELP!

 

[Brianwarnock]

I am glad that you have solved your current problem but my point has been missed in that you do not know, I suspect, how many names etc may be the max, I think that this type of parsing is usually done using an array when the unbound number can give the number of parts to be handled.

I further think that a solution exists somewhere on the forum.

Brian