PROPER function

[Portucale]

Hi,

I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that

StrConv(Left(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",")-1), 3)&", "&StrConv(Mid(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",") + 1), 3) AS CompleteName

Which as you can see is quite a complex statement, any idea how can I simplify it?

Example of a name: VITOR, FONSECA, preferable output, Vitor, Fonseca

All the help and any help are very much appreciated.

Cheers

 

[bob fitz]

Try:
StrConv(Tbl_Telephony_Temp.Agent,3)

 

[pr2-eugin]

Why are you worried about commas in the first place? It would just be fine with comma..

? StrConv("VITOR, FONSECA", vbProperCase)
Vitor, Fonseca

? StrConv("VITOR FONSECA", 3)
Vitor Fonseca

 

[Portucale]

Thanks, but that would give me only the First Name with Uppercase as Vitor, fonseca, when I need to have Vitor, Fonseca

Try:
StrConv(Tbl_Telephony_Temp.Agent,3)

 

[bob fitz]

Thanks, but that would give me only the First Name with Uppercase as Vitor, fonseca, when I need to have Vitor, Fonseca

Have you tried it

 

[pr2-eugin]

Portucale, I am sure Bob has enough experience to suggest it.. Further more I have tested it. Rest assured it will provide the right result.

 

[bob fitz]

Portucale, I am sure Bob has enough experience to suggest it.. Further more I have tested it. Rest assured it will provide the right result.

Yes, I also know that it returns the required result because I tested before I posted. Just curious about the result suggested by the OP.

 

[Portucale]

Hi,

I am not saying it doesn't work, but I must be doing something wrong as it didn't work for me...

This is what i get when I use the StrConv(Tbl_Telephony_Temp.Agent,3)

Agent Expr1001

MCLAUGHLIN,ALLAN Mclaughlin,allan

I am confused!!:banghead:

Thanks guys for the help, and rest assured that I NEVER criticize anyone

 

[bob fitz]

Where are you using it (Querry? Contol Source property?)

 

[Portucale]

Hi Bob,
Using in Access 2010 in a query. But I think I find out why is not working for me... there isn't any blank space after the 'comma'... so seems that is recognizing the whole word as one and not as two.:banghead:

 

[bob fitz]

Try:
StrConv(Replace([Tbl_Telephony_Temp.Agent],","," "),3)

 

[Portucale]

Thanks Bob, works like a dream, although I've just noticed that the first part is in fact the Surname and the Forename is after the comma, is there any way to revert the names

Try:
StrConv(Replace([Tbl_Telephony_Temp.Agent],","," "),3)

 

[pr2-eugin]

You can use this function.

Public Function properName(inputStr As String) As String
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim tmpStr As String
    tmpStr = Mid(inputStr, InStr(inputStr, ",") + 1)
    tmpStr = tmpStr & " " & Mid(inputStr, 1, InStr(inputStr, ",") - 1)
    properName = Trim(StrConv(tmpStr, vbProperCase))
End Function

Put this code in a Standard module. Compile the code, then you can use it in immediate window as..

? properName("Francis , Paul")
Paul Francis

Or in a Query as..

FullName : properName([Tbl_Telephony_Temp].[Agent])

 

[Portucale]

Hi Paul,

Thanks for the help, but I am having issues with putting it to work, see pic, am I doing anything wrong?

It works OK in the Immediate Window, but for some reason not in the query :banghead:

Cheers

 

[pr2-eugin]

If you are using it in SQL query it should be used as..

SELECT ProperName([Tbl_Telephony_Temp].[Agent])[COLOR=Red][B] As FullName[/B] [/COLOR]
FROM Tbl_Telephony_Temp
WHERE Tbl_Telephony_Temp......

Not

SELECT [COLOR=Blue][B]FullName: [/B][/COLOR]ProperName([Tbl_Telephony_Temp].[Agent])
FROM Tbl_Telephony_Temp
WHERE Tbl_Telephony_Temp......

 

[Portucale]

Hi Paul,
I am going crazy, tried so many different things that I haven't realized that the first instance was to be entered in the design mode... Thanks Paul and Bob for the help

 

[pr2-eugin]

No problem ! Glad to help

 

[Portucale]

Hi Paul,

The code works perfect for the names that have a "space" after the 'comma' but in fact I do have some names without the space, like; Vitor,Fonseca and Vitor, Fonseca, can you help?

I managed to get the full name for the names without the space (Vitor,Fonseca, but the result is Vitor Fonseca,) the code I used is:

 tmpStr = tmpStr & " " & Mid(inputStr, 1, InStr(inputStr, ","))

Thanks,

You can use this function.

Public Function properName(inputStr As String) As String
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim tmpStr As String
    tmpStr = Mid(inputStr, InStr(inputStr, ",") + 1)
    tmpStr = tmpStr & " " & Mid(inputStr, 1, InStr(inputStr, ",") - 1)
    properName = Trim(StrConv(tmpStr, vbProperCase))
End Function

Put this code in a Standard module. Compile the code, then you can use it in immediate window as..

? properName("Francis , Paul")
Paul Francis

Or in a Query as..

FullName : properName([Tbl_Telephony_Temp].[Agent])

 

[pr2-eugin]

No, I have validated the code before posting..

[COLOR=Green]'One space [B][U]after[/U] [/B]Comma[/COLOR]
? properName("Francis, Paul")
Paul Francis
[COLOR=Green]'One space [B][U]before[/U] [/B]Comma[/COLOR]
? properName("Francis ,Paul")
Paul Francis
[COLOR=Green]'One space [B][U]before[/U] and [U]after[/U][/B] Comma[/COLOR]
? properName("Francis , Paul")
Paul Francis
[COLOR=Green]'[U][B]No space[/B][/U] but a Comma[/COLOR]
? properName("Francis,Paul")
Paul Francis

It works fine for me..

 

[Portucale]

Yes it works also for me, but when I run the query it gives me a Run-time error '5': Invalid procedure or argument.No idea what could be possible causing that :banghead: