Case Conversion

[andy_dyer]

Hi i've trawled through this code from this post...

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=28343&highlight=capitalise+first+letter

Copied it to a module (is this right?)

And am trying to call it from the AfterUpdate part of my field that I would like converted (is this right?)

Using this code:

Private Sub txtSURNAME_AfterUpdate()
Call SmartCase(Me.txtSURNAME)
End Sub

Can someone please enlighten me as to what I am doing wrong?

Thanks

Andy

 

[pono1]

Andy,

You've got the module part right.

But I think you want to use the Before Update event (your form yells out "I'm just about to update this blasted record. Do you want to do anything?" the second before it saves a record) and code it like this:

Me.txtSURNAME = SmartCase(Me.txtSURNAME)

Regards,
Tim

 

[andy_dyer]

Ok that seems to be doing something but there appears to be an error in the code:

Loop Until (Mid(ConvertStr, mPointer, 1) = " ") Or (Mid(ConvertStr, mPointer, 1) = "-") Or (mPointer > Len(ConvertStr))

It doesn't like this line of code...

Why??

Thanks

Andy

 

[andy_dyer]

It doesn't like anyline with "&gt" in it actually....

 

[andy_dyer]

That was &gt

(no idea why the post changed on being submitted...)

 

[andy_dyer]

I mean the characters:

& g t

(have to put spaces in otherwise it changes!)

 

[andy_dyer]

Help I'm still Stuck!!

Still cannot work this out...

Can anyone suggest where I am going wrong?

Thanks

Andy

 

[billyr]

Try this. (I probably plagerized the same function you are trying to make work.)
Me!yourname=CorrectName(LCase(me!yourname))

Public Function CorrectName(ByVal strName As String) As String
On Error GoTo err_CorrectName
Dim intCounter As Integer
strName = StrConv(strName, vbProperCase)
'Look for and fix "Mc"
If InStr(1, strName, "Mc") Then
strName = Left(strName, InStr(1, strName, "Mc")) & _
StrConv(Mid(strName, InStr(1, strName, "Mc") + 3), 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
'Look for and fix "O'xxxx"
If InStr(1, strName, "'") Then
If InStr(1, strName, "O") Then
strName = Left(strName, InStr(1, strName, "O") - 1) & "O'" & _
StrConv(Mid(strName, InStr(1, strName, "O'") + 2), vbProperCase)
Else
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
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

 

[andy_dyer]

Thanks for responding!

I have now partially fixed the problem!!

adams now becomes Adams

adams-jones now becomes Adams-Jones

macadams now becomes MacAdams

so far so good...

mcadams now becomes MDams!!

Any idea why?

Thanks

Andy

 

[Mile-O]

In the Mc part change the +3 to either +1 or +2 (most likley +2)

One of these will fix it.

 

[andy_dyer]

Just from trying to work out why the "Mac" worked but the "Mc" didn't I changed the "Mc" code to:

'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

And now it works!!

Thanks

Andy

 

[andy_dyer]

Hoping that someone can suggest a qucik fix...

The code is working fine for names, but I wanted to apply the same code to towns.

i.e. kingston-upon-thames should capitalise as Kingston-upon-Thames

The code for this bit is:

If InStr(1, strName, "-") Then
strName = StrConv(Left(strName, InStr(1, strName, "-") - 1), vbProperCase) & _
"-" & StrConv(Mid(strName, InStr(1, strName, "-") + 1), vbProperCase)
End If

by using this code I get Kingston-Upon-thames

Is there someway to tell it to look for two hyphens and if this is the case then capitalise the third word and not the second?

Thanks

Andy

 

[Mile-O]

If you look at the code on the original link you posted, you'll see this part of the procedure:

'Small words - do not capitalise at all unless it is the first word in the string
    If (InStr("A-An-And-At-In-Is-Or-The-Of-", Trim(mWord)) > 0) And ((mPointer - Len(mWord)) > 0) Then
        '(hyphens for Jack-in-the-Box - NB: you can customise this list)
        mWord = LCase(mWord)
    End If

For places, just slip this in:

'Small words - do not capitalise at all unless it is the first word in the string
    If (InStr("A-An-And-At-In-Is-Or-The-Of-[b]On[/b]-[b]Under[/b]-[b]Upon[/b]-", Trim(mWord)) > 0) And ((mPointer - Len(mWord)) > 0) Then
        '(hyphens for Jack-in-the-Box - NB: you can customise this list)
        mWord = LCase(mWord)
    End If

Out of interest, does that > pointer thingy only work in Access 2000 and above as it causes an error in A97. Looks horrible, anayway.

 

[andy_dyer]

Yes the whole reason I didn't use that code and ended up using billyr's suggestion was because of the error after error I got for the > and I am using Access 2000.

So this still doesn't work now either.

Can you show me how I can get this to work or how I can change billyr's code?

 

[Mile-O]

Yes the whole reason I didn't use that code and ended up using billyr's suggestion was because of the error after error I got for the > and I am using Access 2000.

Hit Ctrl & H

Select Current Module

Find: %gt;
Replace: >

Simple, really.

Or, I'll have a look at billy's.

 

[andy_dyer]

Ah ha!!

Now that works ok!

I still have both sets of code in my module as the original code looks way too complicated for me to play with to get it capitalise correctly for hyphenated names and also the Mac that you pointed out that was missing.

I have several double-barrelled names and one or two Mac's as well.

If you are willing to help me add a few more lines of code to the original code then I will be able to remove the later from my database........

But at the very least everything seems to be working just fine!

 

[Mile-O]

If you are willing to help me add a few more lines of code to the original code then I will be able to remove the later from my database........

I struggle to read other people's code if they use naming conventions that I don't. I find that original code a pain to read.

I'm writing a Validation class module just now because I'm sick of having to go looking for different subs and functions whenever I need them for a new database.

Once I've done it I'll post back - it's designed to validate email addresses, correct names, and some other stuff.

 

[andy_dyer]

Sounds a wonderous thing indeed!!

Thanks again for your help, and as I said at least everything is working even though behind the scenes it isn't the tidiest!!

Keep me posted!!

 

[namliam]

Once I've done it I'll post back - it's designed to validate email addresses, correct names, and some other stuff.

Not much to validate about email adresses is there?

- no spaces
- 1 @
- 1 dot after the 1 @

That about it aint it?

Regards

 

[Mile-O]

Not much to validate about email adresses is there?

- no spaces
- 1 @
- 1 dot after the 1 @

I saw a lot more to it: here.

P.S. You can have more than one dot after the @ .

somebody@somewhere.co.uk being an example.