Solved

Convert Uppercase to Mixed Case

Posted on 2011-03-24
11
384 Views
Last Modified: 2013-11-05
I have data in a column that is Uppercase.  I want it to be mixed case.  Example:

SMITH - should be Smith
JONES - should be Jones

Is there a query I can run against my data that would convert it all?

table name = tblPeople
table field = txtLastName
0
Comment
Question by:gcgcit
  • 5
  • 3
  • 3
11 Comments
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 200 total points
ID: 35210015
StrConv("MY STRING",3)  produces My String
0
 
LVL 75
ID: 35210020
Make a BACKUP first, then try this:

UPDATE Table1 SET Table1.YourField = StrConv([YourField],3);


mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35210032
UPDATE tblPeople SET txtLastName = StrConv(txtLastName,3);
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 35210035
Specifically

UPDATE tblPeople SET tblPeople.txtLastName= StrConv([txtLastName],3);
0
 

Author Comment

by:gcgcit
ID: 35210150
How can I do this in query builder?

I made it an update query.

Fieldname: txtLastName
Table: tblPeople
Update To: StrConv([txtLastName],3)

But it prompts me with a message box asking for a value of txtLastName when I try to run it.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:gcgcit
ID: 35210168
Ignore that last note, it was a typo on my part!  Got it to work thanks guys!
0
 

Author Closing Comment

by:gcgcit
ID: 35210186
Gave slightly more points to DatabaseMX because GrayL's syntax was off for a table - its not " " but [ ] also DatabaseMX suggested making a backup which I'm glad I did because the first time I toasted my data with the " ".  Thanks to both of you for such a quick response! :)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35210237
Correction - my syntax was correct as long the name did not contain spaces or any 'strange' characters - underlines excepted.
0
 
LVL 75
ID: 35210248
You are welcome.  ALWAYS  ... make a backup first before running Action queries presented here or elsewhere :-)

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35210257
In the StrConv() example, "MY STRING" was a variable - not the name of a field.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35210268
Thanks, glad to help.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now