Upper Case to lower 1

[goslincm]

Hi, I have a database I just received and have to work with. All tables and all fields are in upper case. Is there an easy way to change this from upper to lower, yet capitalize the first letter of each field?

 

[AlexCuse]

You are in luck:

faq702-6510

Ignorance of certain subjects is a great part of wisdom

 

[goslincm]

I see where that will make the first letter capital but what about the remaining letters, they will change from upper caps to lower?

 

[AlexCuse]

Ah my fault. Try this:

UCase(Left(str, 1)) & LCase(Right(str, Len(str) - 1))

(replace str with your column name)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[goslincm]

You know, I just went in and merely used the < format in the table structure and it it all to lower case. But, now think perhaps an action query will make my first character upper case?

Can you tell me how the action query would be set up?

 

[AlexCuse]

Something like this (not tested):

update [table]
set [column] = UCase(Left([column], 1)) & LCase(Right([column], Len([column]) - 1))

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[frumpus]

I am having the exact same issue but when i run the update query below i get an error about 'type conversion' and if i allow it to go ahead and run it clears all the data from the last name field completely. any thoughts on why this isn't working?

UPDATE [names] 
SET [Last Name] = (Left([Last Name],1) & LCase(Right([Last Name], Len([Last Name] -1)))),
    [First Name] =UCase([First Name]);

 

[frumpus]

oh, ignore the [first name] update portion of that. once i get the code working i'll be applying it to both first and last names in the table.

 

[PHV]

Perhaps this ?
UPDATE [names]
SET [Last Name] = UCase(Left([Last Name],1)) & LCase(Mid([Last Name], 2))
, [First Name] =UCase([First Name])
WHERE Not ([Last Name] Is Null);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frumpus]

BINGO

thanks PH