Trouble flipping name field in query!!!!

[GOVMATE]

Hello I'm new to queries!

I'm having a problem writing an update query that will look at a name field ie (Dan Wilson Sr) and flip the name to a shortname ie (Wilson Sr Dan) with the criteria based to only flip the name if it contains Jr or Sr.
Below is an example of a select query I was using to test before updating my table.

SELECT *
FROM [Goodrec - All]
WHERE (instr(shortname,' JR')>0 Or instr(shortname,'SR')>0 Or instr(shortname,' III')>0) And instr(shortname,' MD')=0;

This query provides the following result for example, if name is "PAUL D TAYLOR SR" it is flipped to "SR PAUL D TAYLOR." I need shortname to display it like this "TAYLOR SR PAUL D"

Thanks in advance for any assistance!

 

[Lister]

Without writing a function to handle this quite complex logic, if you are able, would it be possible to pick the name up in it's components and then do what you want with them?
ie: [FirstName],[MiddleName],[LastName],[Prefix]
"David","Hector","Jonson","Sr"
Instead of using [UserName] "David H Jonson Sr"
So based on the value returened in [Prefix], you would be able to join the name which ever way you liked.

IIf([Prefix]='Sr',[LastName]&" "&[Prefix]&" "&[FirstName]..... etc
Trolling through strings looking for one value is not fun, and not recommended.

 

[GOVMATE]

Without writing a function to handle this quite complex logic, if you are able, would it be possible to pick the name up in it's components and then do what you want with them?
ie: [FirstName],[MiddleName],[LastName],[Prefix]
"David","Hector","Jonson","Sr"
Instead of using [UserName] "David H Jonson Sr"
So based on the value returened in [Prefix], you would be able to join the name which ever way you liked.

IIf([Prefix]='Sr',[LastName]&" "&[Prefix]&" "&[FirstName]..... etc
Trolling through strings looking for one value is not fun, and not recommended.

Unfortunately I can't pick the names up in components because there are too many. I'm dealing with very dirty tables that I have to correct the formatting for, so that it can be loaded into other systems for processing.

 

[CraigDolphin]

Have a look at the attached db for one solution. The query checks for a potential prefix, and calls a public vba function to flip the name if one is found, otherwise it displays the name. I haven't spent time to deal with potential bugs like nulls so there's no guarantee it will work in all cases without modification. It's also probably not the most efficient code, but it seems to work ok so far.

HTH

 

[GOVMATE]

Have a look at the attached db for one solution. The query checks for a potential prefix, and calls a public vba function to flip the name if one is found, otherwise it displays the name. I haven't spent time to deal with potential bugs like nulls so there's no guarantee it will work in all cases without modification. It's also probably not the most efficient code, but it seems to work ok so far.

HTH

Ok thanks Craig I'll try it out this out at home...since it seems I need home work today..lol!!! I'm leaving my office for the day!!!

 

[burrcm]

If you are preparing this mess for porting to other systems, now would be a great time to "bite the bullet" and normalize the tables. How many is "too many"? Things like pasting into Excel and doing some text to columns come to mind as a first move. Excel 2007 handles 1.1 million rows. Not as bad as it sounds, copy the db first for safe keeping, then - add a temp key column to the table - numeric, not autonumber, then take this column to excel with the text data, do the text to columns, sort it so the similar items are together, separate the fields as needed, put it all back in the original order and put it back in the db in new columns created for the purpose. Much fun and should only take a day or so...

Chris B

 

[GOVMATE]

If you are preparing this mess for porting to other systems, now would be a great time to "bite the bullet" and normalize the tables. How many is "too many"? Things like pasting into Excel and doing some text to columns come to mind as a first move. Excel 2007 handles 1.1 million rows. Not as bad as it sounds, copy the db first for safe keeping, then - add a temp key column to the table - numeric, not autonumber, then take this column to excel with the text data, do the text to columns, sort it so the similar items are together, separate the fields as needed, put it all back in the original order and put it back in the db in new columns created for the purpose. Much fun and should only take a day or so...

Chris B

I appreciate the input, it will be put use!
Thanks Chris!

 

[GOVMATE]

Ok thanks Craig I'll try it out this out at home...since it seems I need home work today..lol!!! I'm leaving my office for the day!!!

Hey Craig,

I tried your query...tweaked it alittle and it worked. Stills needs some tweaking because it didn't catch all of the JR's for some reason?????

Thanks

 

[GOVMATE]

Ok thanks Craig I'll try it out this out at home...since it seems I need home work today..lol!!! I'm leaving my office for the day!!!

Craig,

I was able to get this code to work at home but not at work. Is there a particular library that I need to reference in order for this to work? If so, which one?

Thanks!

 

[CraigDolphin]

Hmm. No special library should be required....I wrote it in Access 2000 but have not tested it in Access 97 so if you're using 97 at work maybe that's the problem.

The only libraries referenced in the version I created were:
Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Otherwise, what error message are you getting? And if you can provide me with some example data where the JR's are not being caught maybe I can fix that.

 

[GOVMATE]

Hmm. No special library should be required....I wrote it in Access 2000 but have not tested it in Access 97 so if you're using 97 at work maybe that's the problem.

The only libraries referenced in the version I created were:
Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Otherwise, what error message are you getting? And if you can provide me with some example data where the JR's are not being caught maybe I can fix that.

Ok Craig,

I'm using Access 2003 @ home and work. The only reference that I have different from your is Microsoft Access 11.0 Object Library. I was told by some others in this forum that "flipname" is not a valid function of vba or sql??? I'm not sure how it works!!! The error that I rec'd when running the script is: "Undefined function "Flipname" in expression. I have provided a sample mdb attached with some example names. The field called name has already been flipped into the shortname field. My only problem now is adjusting all JR, SR, II, III to be displayed correctly in the shortname field. example: "Felton JR David"

script below

UPDATE [Goodrec-ALL] SET [Goodrec-ALL].SHORTNAME = IIf(InStr(UCase([shortname])," SR")>0 Or InStr(UCase([shortname])," JR")>0 Or InStr(UCase([shortname])," II")>0 Or InStr(UCase([shortname])," III")>0,FlipName(UCase([shortname])),UCase([shortname]));

 

[CraigDolphin]

Ack. I think you misunderstood my original post.

The reason you're getting the error is that, as I explained when giving you the example db, in the example I provided you there is a VBA module containing a public function called Flipname. You need to copy that module into the db you intend to use the function in.

A public function is NOT the same thing as a library which you reference via the VBA editor. A public function is simply a function that is available anywhere within the database where its code module occurs. A private function is only available within the code module where it is located. Some functions are native to Access (eg, UCase(), Date() etc) and some are written by a user (like FlipName() in this case).

 

[CraigDolphin]

I don't see any db attached to your post. Also, I see that you have added another prefix to the list (II) so the FlipName fucntion will need to be amended to deal with that (as follows)

Public Function FlipName(myname As String) As String

Dim prefix_position As Integer
Dim prefix_length As Integer
Dim prefix As String
Dim mytext As String
Dim mylastname As String
Dim myremnantname As String

If InStr(myname, " SR") > 0 Then
    prefix_position = InStr(myname, " SR")
    prefix_length = 3
    prefix = "SR"
End If

If InStr(myname, " JR") > 0 Then
    prefix_position = InStr(myname, " JR")
    prefix_length = 3
    prefix = "JR"
End If

If InStr(myname, " II") > 0 Then
    prefix_position = InStr(myname, " SR")
    prefix_length = 3
    prefix = "II"
End If

If InStr(myname, " III") > 0 Then
    prefix_position = InStr(myname, " III")
    prefix_length = 4
    prefix = "III"
End If

mytext = Trim(Left(myname, Len(myname) - prefix_length))

mylastname = Right(mytext, InStr(StrReverse(mytext), " ") - 1)
myremnantname = Left(mytext, Len(mytext) - Len(mylastname))
FlipName = Trim(mylastname & " " & prefix & " " & myremnantname)

End Function

 

[GOVMATE]

Ack. I think you misunderstood my original post.

The reason you're getting the error is that, as I explained when giving you the example db, in the example I provided you there is a VBA module containing a public function called Flipname. You need to copy that module into the db you intend to use the function in.

A public function is NOT the same thing as a library which you reference via the VBA editor. A public function is simply a function that is available anywhere within the database where its code module occurs. A private function is only available within the code module where it is located. Some functions are native to Access (eg, UCase(), Date() etc) and some are written by a user (like FlipName() in this case).

Ok,

Thanks for explaining the difference between the private and public function. I see now that the reason why the script worked at home is because I imported a table into your db example which had the public function defined under the modules.

Thanks!!!!

 

[GOVMATE]

I don't see any db attached to your post. Also, I see that you have added another prefix to the list (II) so the FlipName fucntion will need to be amended to deal with that (as follows)

Public Function FlipName(myname As String) As String

Dim prefix_position As Integer
Dim prefix_length As Integer
Dim prefix As String
Dim mytext As String
Dim mylastname As String
Dim myremnantname As String

If InStr(myname, " SR") > 0 Then
    prefix_position = InStr(myname, " SR")
    prefix_length = 3
    prefix = "SR"
End If

If InStr(myname, " JR") > 0 Then
    prefix_position = InStr(myname, " JR")
    prefix_length = 3
    prefix = "JR"
End If

If InStr(myname, " II") > 0 Then
    prefix_position = InStr(myname, " SR")
    prefix_length = 3
    prefix = "II"
End If

If InStr(myname, " III") > 0 Then
    prefix_position = InStr(myname, " III")
    prefix_length = 4
    prefix = "III"
End If

mytext = Trim(Left(myname, Len(myname) - prefix_length))

mylastname = Right(mytext, InStr(StrReverse(mytext), " ") - 1)
myremnantname = Left(mytext, Len(mytext) - Len(mylastname))
FlipName = Trim(mylastname & " " & prefix & " " & myremnantname)

End Function

Ok thanks,

Can you tell me if I copy the following statement and add it the module only changing the prefix_length = 3 instead of 4 after the same statement, will cause the query to catch more instances of space variations to correct and flip more names correctly?

 

[CraigDolphin]

if I copy the following statement and add it the module only changing the prefix_length = 3 instead of 4 after the same statement, will cause the query to catch more instances of space variations

No. The prefix_length is required to chop up the string at the right place. Instead, what I would do in your shoes would be to get rid of double spaces inside of your string, and extraneous terminal spaces, first then run the flipname function on the results of that process.

So, if your original field is called 'longname'
CleanedName: Trim(Replace([longname]," ", " ")) 'note there are two spaces inside the first quotes, and only one in the second set of quotes (darned formatting of this board compresses them)
(you could either use this as a query of the original table, or else update the values in the original table's field.

Then, taking the results of that step, and use it as the source field for your update query.

 

[GOVMATE]

No. The prefix_length is required to chop up the string at the right place. Instead, what I would do in your shoes would be to get rid of double spaces inside of your string, and extraneous terminal spaces, first then run the flipname function on the results of that process.

So, if your original field is called 'longname'
CleanedName: Trim(Replace([longname]," ", " ")) 'note there are two spaces inside the first quotes, and only one in the second set of quotes (darned formatting of this board compresses them)
(you could either use this as a query of the original table, or else update the values in the original table's field.

Then, taking the results of that step, and use it as the source field for your update query.

Alright, sounds good I will try that!!!!!!!! Thanks