Replies

Is such special handling something best left to an accomplished programmer (something I’m not) or are the solutions simple enough that I can understand and implement them? BTW, I have to deal with close to 40,000 records, of which a few hundred change each month. There’s no easy way to know each month which records may have these unique naming characteristics.

I spoke a bit too soon. When I tried it on a larger set of data, I noticed a couple of problems. First, the routine doesn’t deal properly with the following data: JOSLIN,BARBARA A ETAL. I get the following result: JOSLINE BARBARA A ET AL when it should be BARBARA A JOSLIN ETAL. I think we need to add a procedure that searches for the unique endings (TR, ETAL) and stores that to a variable, say ENDING. Then if there’s a comma, the string to the left of the comma gets stored to a variable LASTNAME. Then we store the string data to the right of the comma to a variable up to but excluding the unique ending string, if it exists, and call that FIRSTNAME. Then we rebuild the name as FIRSTNAME &” “& LASTNAME& ” “& ENDING. I don’t know how to code this and don’t know if I’m on the right track, but these are my thoughts.

Second, the procedure doesn’t deal properly with last names that begin with TR: AGUILAR,TRINIDAD or BRISENO,TRIFUNO & ANA TR (the last one’s really nasty). If we could deal with the first of the two it would be great. I can accept that a canned routine probably can’t deal with all possible versions of the data.

I’ve attached another copy of the database. Note that I added lines to the code trying to cover more versions of the ending, but it didn’t seem to help.

I spoke a bit too soon. When I tried it on a larger set of data, I noticed a couple of problems. First, the routine doesn’t deal properly with the following data: JOSLIN,BARBARA A ETAL. I get the following result: JOSLINE BARBARA A ET AL when it should be BARBARA A JOSLIN ETAL. I think we need to add a procedure that searches for the unique endings (TR, ETAL) and stores that to a variable, say ENDING. Then if there’s a comma, the string to the left of the comma gets stored to a variable LASTNAME. Then we store the string data to the right of the comma to a variable up to but excluding the unique ending string, if it exists, and call that FIRSTNAME. Then we rebuild the name as FIRSTNAME &” “& LASTNAME& ” “& ENDING. I don’t know how to code this and don’t know if I’m on the right track, but these are my thoughts.

Second, the procedure doesn’t deal properly with last names that begin with TR: AGUILAR,TRINIDAD or BRISENO,TRIFUNO & ANA TR (the last one’s really nasty). If we could deal with the first of the two it would be great. I can accept that a canned routine probably can’t deal with all possible versions of the data.

I’ve attached another copy of the database. Note that I added lines to the code trying to cover more versions of the ending, but it didn’t seem to help.

Perfect! Thanks so much for your response. You saved me lots of headaches.

Perfect! Thanks so much for your response. You saved me lots of headaches.

Your sample database and module did the trick. I modified the code in the last line as follows–

ChangeName = Trim(Mid$(strName, intPosComma + 1)) & ” ” & Trim(Left$(strName, intPosComma – 1)) & strSpecial

That way it deals properly with data such as

Smith,Jones
Smith , Jones

etc.

Thanks very much for your help.

Jeff

Thank you for your response. I’ve tried to use your code, but can’t get it to work. I’ve never used VBA before. I entered the code in a Module window and saved the results. Question 1: Any idea why 2 module windows with seemingly identical contents opened up? I created a new Update Query and put the following phrase in the Update To: criteria–

ChangeName([Owner])

But when I ran the query I received an error that said–

Undefined function “ChangeName” in expression.

Question 2: What am I doing wrong?

I have a module named ChangeName with your code in it. I have a Test table with 2 fields: Owner (populated with a few dummy records) and NewOwner (empty and waiting to be populated by the Update query). The Update Query only has the Test table associated with it. The update field is NewOwner.

Thanks for your help.

A follow up question. If the data is as follows:

Smith, John
Jones Industries

what would InStr(1,[Owner]) return? Why is the number 1 in front of the field name? I don’t see how this works since InStr searches for something within a string, and 1 isn’t a string as used in the argument.

Thanks for your help.

Thank You! I had no idea it returned the position. I’ve looked all through the online help, the Que Access 2000 book, and the Access 2000 manuals without finding a description of the function. Maybe I overlooked it.

Thanks again.

I found the problem–my sound card was not fully supported by XP Pro. I got system sounds fine, and some music, cut cd’s were no good. Went out and bought a SB 16 PCI and everything’s fine now.

I’ve tried this with new and old Cd’s. No difference. I never had this problem when I used NT Workstation. I have the latest hardware drivers and software patches. Any other ideas what might be going on?

Eureka! I removed the computer from the domain and assigned it to the workgroup, now I can add printers. No idea why this matters, but at least I have a solution. Thanks again for your help.

I only understood some of the first part. I still don’t really understand what a domain is. If I’ve assigned the user ID administrator rights on both the NT server and the XP machine, how can there be an issue of access privileges?

I did have printer access before the rebuild.

How do I turn off the firewall?

Do you know how to remove the computer from the domain and merely assign it to a workgroup?

Thanks very much for your time and assistance on an apparently difficult problem.

What’s the difference? Are network admin rights assigned under NT, XP or Novell? I have access to the NT Server (I’m the administrator), I have access to the XP Pro machine (my own), but I don’t have access to Novell naming or rights.

Thanks for the info.