Updating partial field?

[vlk234]

Is there any way to update a partial field in Access. If I have the text:

SMITH, JOHN, MD

When proper cased, the field looks like this:

Smith, John, Md

What I need it to look like is:

Smith, John, MD

Is there any way to replace Md with MD while leaving the original name intact? I've tried several things with no success. The proper case scripts work well as long as you work from the left. Working from the right is an entirely different story.

 

[lupins46]

Applying proper case to names leads to all sorts of anomolies and undesirable outcomes. This is just one.
If all your names are MDs then you could create a function to convert everything except the last two characters , but I suspect this is not the case.

If you've nothing better to do with your life -) then you can spend it creating a function which searches names for all the situations that you don't want proper case to apply exactly as it does by default.I'm sure that Cecil B de Mille, John the Baptist, Mary O'Hara, Justine Henin-Hardenne, Quincy MD/ME and many more will be grateful.

 

[vlk234]

Just to be difficult, how would I exclude the last 4 characters of a field from being proper cased?

 

[Golom]

Something like
[tt]
Mid ( NameString, 1, Len(NameString) -4 ) = _
SrtConv (Mid ( NameString, 1, Len(NameString) -4 ), vbProperCase)
[/tt]

 

[vlk234]

Thank you, I'll try it!

 

[vlk234]

When I use this in my scrpt

For I = 2 To Len(stOneLine) - 4

The last four characters are ignored completely and not returned in their original state. I think we're on the right track though. Thanks for getting me this far.

 

[Golom]

You didn't show the code inside the for loop but if you're building a new string using character-by-character processing then indeed you will not get the last 4 characters because you never got to them. The code I provided doesn't require character-by-character processing.

BTW: SrtConv should be StrConv

 

[vlk234]

Thanks for the correction. Yeah, I did figure out that I would never get to the last 4 characters. I've only used a few scripts and am still trying to figure all of this out. So far, your script is not leaving the last 4 characters in their original state. Could this be because of differences in name lengths? I apologize for being such a pest, but I am learning! Thanks again.

 

[Golom]

There should be no "differences in length". The statement that I provided just replaces all but the last 4 characters with another "proper cased" string of the same length. What result are you getting?

 

[BNPMike]

Updating "partial fields" is a bit like going through a speed camera. Don't tell anybody. You might get away with it.

What you have is First Name, Last Name, Qualifications ie at least three fields. People are helping you bodge it here on Tek-Tips but truthfully you're just going to be back here in a few days with another tricky problem.

It's like alcoholism. To overcome it you've first got to face up to it. The fact is your database is unrelational.

Bite the bullet and make your table fields hold atomic data. I can't guarantee you a happy normal life, but it'll be a start on the right track.

 

[vlk234]

Actually, I'm getting a Run-Time Eror '13'

Type mismatch

when I run the script as is. If I ignore the error, I get a validation violation. I've been playing around with it. I know I'm doing something wrong.

 

[vlk234]

Thanks, BNPMike. I actually did think of that. Unfortunately, I inherited the database and was trying to make it work as it was set up. Actually, the name field has four entities. Last Name, First Name, Mid Init, Title... We may end up doing that yet.