Link to home
Create AccountLog in
Avatar of binaryman101
binaryman101Flag for United States of America

asked on

Change cASE on all data in a column

I have looked on this site for trying to change the case in Microsoft Access 2007 tables and every solution I try it doesn't help.  I am asking for your help.  I have about 200 records with this in there.  I would like to develop a macro to fix this in the future years.  They like to have all the records uppercase for some records and not for others.  What they would like to do is just hit a macro and have it fix it every year back so they are all normal:

Current:  MACK
Should be:  Mack

I am new to this and would love as basic help as I could.  I have designed many databases in the past and just seem to be struggling on this request.
Avatar of ee_rlee
Flag of Philippines image

hi, try this

Update tablename set col=StrConv (col, 3)
if I understand you correctly - you want Initial capitals.

So you'd create a Function in a Module that would be something like  (you'd probably want to add some error checking, but here's the basics

Function ChangeCase(source as string) as string

ChangeCase = ucase$(left(source,1)) + lcase$(right(source,len(source)-1))

End If

End Function

now, create an update query which updates the field using the ChangeCase function you created above (ie.. when you change the query type to update, you'll see a row labeled 'Update To' in the query grid, use "ChangeCase([field])" .. replaceing 'field' with your field name)

last, create a macro that runs the update query
Avatar of jpipkins

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of binaryman101


Great job explaining step by step.  Thanks a lot!