Lower To Upper Case Conversion

[NewsGroups]

I would like to loop thru my db and change the FName, MI, LName fields to a
standardized format.

If the first letter is in lower case I would like to change it to upper
case.
lisa would be converted to Lisa

I also need to look for those that are in all Upper case can convert them to
the proper format
LISA would be converted to Lisa.

MI would use the lower to upper routine
l. convert to L.
L. do not convert.

I would image that someone has done this before.

So that I would not have to run this routine again is there a input
formatting that can be done for new records?

I would think that the only other consideration would be those LNames that
are like MCCARTHY or mccarthy
which would have to be converted to McCarthy.

Any help would be appreciated.

 

[Lynn Trapp]

The function for that is StrConv([FName],VBProperCase)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

 

[NewsGroups]

I tried to undersand this concept by creating a query and running it on just
one record. I get an undifined function when I use the following code.

UPDATE dbo_Contacts SET dbo_Contacts.Company = vbProperCase([Company])
WHERE (((dbo_Contacts.CompanyID)=14079));

I have tried Proper, ProperCase and the above syntax but all come back with
the same error.

Please advice where I am going wrong. Again thanks for your reply.

The function for that is StrConv([FName],VBProperCase)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

I would like to loop thru my db and change the FName, MI, LName fields

to

a
standardized format.

If the first letter is in lower case I would like to change it to upper
case.
lisa would be converted to Lisa

I also need to look for those that are in all Upper case can convert

them

to
the proper format
LISA would be converted to Lisa.

MI would use the lower to upper routine
l. convert to L.
L. do not convert.

I would image that someone has done this before.

So that I would not have to run this routine again is there a input
formatting that can be done for new records?

I would think that the only other consideration would be those LNames that
are like MCCARTHY or mccarthy
which would have to be converted to McCarthy.

Any help would be appreciated.

 

[Ken Snell]

The VB instrinsic constants don't work in queries. Change vbProperCase to 3.

--
Ken Snell
<MS ACCESS MVP>

I tried to undersand this concept by creating a query and running it on just
one record. I get an undifined function when I use the following code.

UPDATE dbo_Contacts SET dbo_Contacts.Company = vbProperCase([Company])
WHERE (((dbo_Contacts.CompanyID)=14079));

I have tried Proper, ProperCase and the above syntax but all come back with
the same error.

Please advice where I am going wrong. Again thanks for your reply.

The function for that is StrConv([FName],VBProperCase)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

I would like to loop thru my db and change the FName, MI, LName

fields

to them

 

[Ken Snell]

Also, you didn't use the function as Lynn said to use it:

UPDATE dbo_Contacts SET dbo_Contacts.Company = StrConv([Company], 3) WHERE
(((dbo_Contacts.CompanyID)=14079));


--
Ken Snell
<MS ACCESS MVP>

I tried to undersand this concept by creating a query and running it on just
one record. I get an undifined function when I use the following code.

UPDATE dbo_Contacts SET dbo_Contacts.Company = vbProperCase([Company])
WHERE (((dbo_Contacts.CompanyID)=14079));

I have tried Proper, ProperCase and the above syntax but all come back with
the same error.

Please advice where I am going wrong. Again thanks for your reply.

The function for that is StrConv([FName],VBProperCase)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

I would like to loop thru my db and change the FName, MI, LName

fields

to them

 

[Lynn Trapp]

VBProperCase is not a function but a constant. Since you said you wanted to
"loop" through your records, I assumed you were going to be doiong this in
a module rather than in a query. Use the Update statement that Ken Snell has
given you below and you should be in fine shape.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

I tried to undersand this concept by creating a query and running it on just
one record. I get an undifined function when I use the following code.

UPDATE dbo_Contacts SET dbo_Contacts.Company = vbProperCase([Company])
WHERE (((dbo_Contacts.CompanyID)=14079));

I have tried Proper, ProperCase and the above syntax but all come back with
the same error.

Please advice where I am going wrong. Again thanks for your reply.

The function for that is StrConv([FName],VBProperCase)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

I would like to loop thru my db and change the FName, MI, LName

fields

to them

 

[NewsGroups]

Just wanted to post back and tell each of you that your response helped me
tremendously!!

20,000 records < 30 seconds.

You saved me a ton of time and the correspondence we send to our clients
will never again look like we don't care about our data.

Again THANK YOU

Hope this finds you, and yours, in the best of health and spirit this
holiday season.

 

[Jim Harrison]

Something along this line has worked for me when making a new entry in a
field. Only problem is names like McCarthy as it only does the very first
letter.
I don't have it written in front of me by I have used a similar formula in a
"make table query" to update an existing field.

Expr1: strYourField = StrConv([strYourField],3)

Private Sub YourField_LostFocus()
strYourField = StrConv([strYourField],3)
End Sub

jim

 

[android]

Wow I thnk that these posts will help me. Don't know why but I starte
entering all my records in CAPS so now I have hundreds to change t
sentence case. I hope that your posts will help.

THANKS (I HOPE)

Mike COwi