Auto Correct last names with "MAC"

[TomKat]

Having problems getting this portion of the module to work.

'Look for and fix "Mac"
If InStr(1, strName, "Mac") Then
strName = Left(strName, InStr(1, strName, "Mac") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Mac") + 2), vbProperCase)
End If

No matter what you type, it will not Capitalize the 4 letter in the name
after "Mac". I have tried changing the "+ 2" to "+ 3".

Is there something wrong?

 

[dkinley]

Works here ... correct letter position is used on +3

-dK

 

[TomKat]

Not working for me.....

Is there a way to fix the names in the table itself vice the forms?

I have this in a simple query and it works fine less the "Mac" part.

Smartcase-Function: Smartcase([LastName])

If there was a way to get around all this and fix it in the table itself, that would be better.

 

[dkinley]

I read again, what I don't see is the return. Because it is in a module, suppose the function name was "RenameMac (strName As String) "

After the "End If" in your bit of code (modified with a +3), are you putting something like ...

RenameMac = strName

If you are using in an update query then all you need to do in the "Update To" row is put in ....

RenameMac([FieldName])

Hope that helps.
-dK

 

[dkinley]

I used it on a command button and stuck it in a query just to be sure. I had to add the return but it worked in both areas.

You could use it in a query to go ahead and update all of existing fields. Probably should add it on the AfterUpdate event of the control in the form for those future times when people add names and stuff.



-dK

 

[TomKat]

Here is what I put....

'Look for and fix "Mac"
If InStr(1, strName, "Mac") Then
strName = Left(strName, InStr(1, strName, "Mac") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Mac") + 3), vbProperCase)
End If
CorrectName = strName

CorrectName being the function name...

Still not doing a thing.....

 

[dkinley]

I am sorry, I don't know what to tell you unless it is not a Public function. I am not sure where you are calling it from and whether or not this variable is getting passed around inside a module, etc.

It is working fine for me. I would think that the string is never being evaluated on those known fields that have 'mac' in them.

Perhaps after your If statement you can either put a break point (or a MsgBox "Inside If"). Run it and see if the system stops inside the If.

If that is not true, then you know that the If-Then never being accessed. I would then move that break point between the function name and the If-Then to see if the function is being put into play. If it doesn't break there; this would at least tell you that you are not calling it correctly or the function does not have the proper scope. If it does, you could evaluate strName to see if the proper data is being passed in the immediate window or hover the mouse over the variable to see what data is in it. If that reads correctly, you could "step-into" the following lines and compare what the variable holds during and on the otherside of the If-Then before it passes out of the function.

-dK

 

[raskew]

Try a version of this -

x = "macdonald"
y = instr(x, "donald")-1
? y
 3 
? strconv(left(x,y),vbProperCase) & strConv(mid(x, y + 1),vbProperCase)
MacDonald

HTH - Bob

 

[TomKat]

That is my guess, there is an IF/Then/EndIF for each one (Mc, Mac, O', and -). My guess it is hitting the first IF which is the "Mc" and crapping out on the rest. But then again, the "-" function is working if the user has two last names. No irish names to verify if the "O'" is working unless I add one.

It is all in the module and is pulling from there based on the before update event. It works less the "Mac".

 

[raskew]

How 'bout posting this module you keep talking about. And, by the way, check out your title:

Re: Auto Correct last names with "MAC"

Bob

p.s.: Cruising back thru your previous posts, I can't find any reference to: "IF/Then/EndIF for each one (Mc, Mac, O', and -)". Help me out here.

 

[TomKat]

This is what I obtained from this forum:

Public Function CorrectName(ByVal strName As String) As String
On Error GoTo err_CorrectName
Dim intCounter As Integer
strName = StrConv(strName, vbProperCase)
renameMac = StrConv(strName, vbProperCase)
'Look for and fix "Mc"
If InStr(1, strName, "Mc") Then
strName = Left(strName, InStr(1, strName, "Mc") - 1) & "Mc" & _
StrConv(Mid(strName, InStr(1, strName, "Mc") + 2), vbProperCase)
End If
'Look for and fix "Mac"
If InStr(1, strName, "Mac") Then
strName = Left(strName, InStr(1, strName, "Mac") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Mac") + 3), vbProperCase)
End If
CorrectName = strName
'Look for and fix "Lav"
If InStr(1, strName, "Lav") Then
strName = Left(strName, InStr(1, strName, "Lav") - 1) & "Lav" & _
StrConv(Mid(strName, InStr(1, strName, "Lav") + 2), vbProperCase)
End If
'Look for and fix "O'xxxx"
If InStr(1, strName, "'") Then
strName = Left(strName, InStr(1, strName, "O") - 1) & "O'" & _
StrConv(Mid(strName, InStr(1, strName, "O'") + 2), vbProperCase)
End If
If InStr(1, strName, "O") Then
strName = Left(strName, InStr(1, strName, "'") - 2) & LCase(Mid(strName, InStr(1, strName, "'") - 1, 1)) & "`" & _
StrConv(Mid(strName, InStr(1, strName, "'") + 1), vbProperCase)
End If
'Look for and fix "-"
If InStr(1, strName, "-") Then
strName = StrConv(Left(strName, InStr(1, strName, "-") - 1), vbProperCase) & _
"-" & StrConv(Mid(strName, InStr(1, strName, "-") + 1), vbProperCase)
End If
CorrectName = strName
exit_correctname:
Exit Function
err_CorrectName:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume exit_correctname
End Function

 

[raskew]

Kool -

Could you point to that site where you found this?

Thanks,

Bob

 

[TomKat]

Its in the "Modules & VBA" discussion side. Search for "Case conversion" and it posted by "billyr".

 

[raskew]

No,

How about, since you've been referring frequently to this site, how 'bout you point us to it. Directly, no search and find!

Bob

 

[TomKat]

http://www.access-programmers.co.uk/forums/showthread.php?t=55235&highlight=CorrectName

Towards the bottom... I found it in two seconds searching on "CorrectName"

 

[raskew]

Its in the "Modules & VBA" discussion side. Search for "Case conversion" and it posted by "billyr".

And that has what to do with "CorrectName"?

Bob

Help me out here!

 

[TomKat]

Because this portion of that module:
'Look for and fix "Mac"
If InStr(1, strName, "Mac") Then
strName = Left(strName, InStr(1, strName, "Mac") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Mac") + 3), vbUpperCase)
End If

Is not working for me. Was wondering if there was another way to names
with "Mac....." to read "MacN...." or "MacM..." etc...

Everything else works in that module but the "Mac"

 

[dkinley]

Here is the db ...

I have it on an unbound text box and used it in an update query to show that it is working.

The only thing I can think of is that you either have something else preempting or your VB references aren't correct.

-dK

 

[TomKat]

I did the same thing, put exactly what you did. Created a form just to test the last names and it works fine. Don't know why it will not show in the test query.

Now here's another one for you. Suppose you have a last name that is Macion or Mack or something that the letter after the C is not supposed
to be correct. How do you tell VB not to capitalize those? Is there
additional code to add to tell it to look for "L" or "N" or "M", etc.. then
do the UpperCase.

 

[TomKat]

I finally got it... figured out what I was doing wrong. In my query, I had the "Smartcase" and "Correctname" switched for first and last name. Once I made both using "Correctname" worked great. I also added this
to correct the letter after the "Mac" if it should be capitalized.

So if anyone else could use it, feel free: "I just modified the original Mac if statement"

'Look for and fix "Mac"
'If InStr(1, strName, "Mac") Then
'strName = Left(strName, InStr(1, strName, "Mac") - 1) & "Mac" & _
'StrConv(Mid(strName, InStr(1, strName, "Mac") + 3), vbProperCase)
'End If
'Look for and fix "Maca"
If InStr(1, strName, "Maca") Then
strName = Left(strName, InStr(1, strName, "Maca") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Maca") + 3), vbProperCase)
End If
'Look for and fix "Macl"
If InStr(1, strName, "Macl") Then
strName = Left(strName, InStr(1, strName, "Macl") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Macl") + 3), vbProperCase)
End If
'Look for and fix "Macm"
If InStr(1, strName, "Macm") Then
strName = Left(strName, InStr(1, strName, "Macm") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Macm") + 3), vbProperCase)
End If
'Look for and fix "Macn"
If InStr(1, strName, "Macn") Then
strName = Left(strName, InStr(1, strName, "Macn") - 1) & "Mac" & _
StrConv(Mid(strName, InStr(1, strName, "Macn") + 3), vbProperCase)
End If

Works great now. If I see a name that is added later that requires the change. I will just make another IF statement.

Thanks guys for all your help. I am sssssslllllloooooowwwwwwwlllllllllyyyy getting this.