Make a Field name Upper case on an Access table

I need to know how to make a field in an Access table upper case. I'm not talking about an Access form etc. These are tables maintained by the IT staff who go right into the Access table and make entries. I know the > in the Format of the Field properties will display it in upper case but it will be stored as it was entered. I don't care how it is entered. I need it to be converted and stored in Upper case. I would think it has something to do with the Input Mask on the field properties and UCASE or somehow have a macro run on it. But I can't find it anywhere on the web. All discussions talk about shifting to Upper case on a form which I know how to do.
Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
are you referring  to the name of the field on the table or the information entered to the table?

if you are referring to the information entered to the table, there is no field property to set to UCASE.

you can run an update query to change the field content to Upper case

update tablex
set [fieldname]=ucase([fieldName])
where [fieldname] is not null
0
Chuck LoweAuthor Commented:
I am refering to the information entered into the field. I forgot to add the valid possibilities are K,P or B.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can also use strconv(), but UCASE is simpler.

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chuck LoweAuthor Commented:
@Jim Dettman

I'm familiar with the UCASE on a form but where exactly on the field in the table do I put it?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You don't.  You need to use UCASE() when you put the data into the table.

You can run an update query to change what's already there.

Jim.

and no points please; Rey had your answer.
0
Chuck LoweAuthor Commented:
Thanks guys
 I had all those considered and was rejected by the IT team. You're saying there is no way to convert the field data to upper case on an access table (like on an Access form) without running a query afterward? They won't accept it. I will just force them in a validation rule to enter the correct capital values.
0
Rey Obrero (Capricorn1)Commented:
afaik, None
0
Rey Obrero (Capricorn1)Commented:
< I had all those considered and was rejected by the IT team>

tell them to hit Caps Lock before entering data..
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<You're saying there is no way to convert the field data to upper case on an access table (like on an Access form) without running a query afterward? >>

 I'm not sure I'm understanding jist of the question, but yes that would be correct.  A tables job is to store data exactly as it has been given to it.

 You can display it differently, but when you open a table, what you see is what you've got.

 Note also that you can display it by using a query instead of the table for something.

Jim.
0
Chuck LoweAuthor Commented:
@Rey
 Come on Rey. We're talking about IT people. They are worse at following directions than users.

@Jim
 Jim I get where you're coming  from. But I'm not going to create forms for every table for IT to enter data.

I was under the impression Access 2010 allowed you to create Input masks but I can't get it to work for some reason.

I may try a After Update Data Macro.
0
Rey Obrero (Capricorn1)Commented:
may i ask, why are your IT people are concern about the look (Ucase) of the information entered in the table?

do they understand database?

yes try the Data Macro.. please post here the result.
0
Chuck LoweAuthor Commented:
@Rey
 They're not concerned about the look, they're concerned about the info being in correctly.
These are tables that are maintained by IT that are mostly used as validation/lookup for users. Only IT has access to them not the users.

I understand the arguments about if they want it correct they should enter it correct. That won't fly. And as stated before I'm not going to make forms for every table IT needs access to.
I thought it simple to cut the dragon's head off at the source and do it directly on the field on the table.
But I'll try the Data macro and let you know how it goes.

Thanks.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<And as stated before I'm not going to make forms for every table IT needs access to. >>

 Ah...yikes.   Cardinal rule with Access is; never let your users enter data directly in a table (actually any database for that matter, but for Access, it's even more important).

 JET lacks triggers (although there are data macros, but they are limited), so unless you use SQL Server as a data store, then your stuck using forms to enforce business logic.

 Your asking for trouble if you do this.

Jim.
0
PatHartmanCommented:
I am confused by the confusion.

Use the AfterUpdate event of the control to change the case OR use the BeforeUpdate event of the form.

Me.SomeField = Ucase(Me.SomeField)

OR, if there is a limited set of values, create a combo with the values in the proper case.
0
hnasrCommented:
Try to use Before Change table trigger.
Open table in Datasheet view. Table Tools > Table Tab > Before Events Group > Before Change button

[TableName].[FieldName] = UCase([TableName].[FieldName])

SetField
Name  [TableName].[FieldName]
Value = UCase([TableName].[FieldName])

Open in new window


Repeat for each required field.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
@hnasr
Thank you. This works. That's exactly what I needed.
0
hnasrCommented:
Welcome!
0
alastairparkerCommented:
I am having a very similar problem with Microsoft Access 2010 on my Samsung home laptop PC. I have a database called Invoice that I use to experiment with the various features of Access 2010. Now, some of the fields are two-word fields, with the first having an initial upper-case letter and the second having an initial lower-case letter.

I only found out this case problem today (Friday 14/10/2016), when I was experimenting with a database form. Anyway, I thought I would go into the Database Design View, change the case of the second word in the two-word field names back to initial upper-case (which I prefer) and re-save the database. Simple, right?

Wrong! As soon as I closed and re-opened the database, all of my changed initial upper-case letters in the second word of the two-word field names reverted to lower-case again. Arrrrah! Surely Microsoft Access (all versions) should store the field name in the case it was originally typed in? I have tried this several times today and have come to the conclusion that it's either a bug in Access 2010 (I hope not, after all of the system updates I have gone through) or as Microsoft would say (infamously) "This Behaviour Is By Design" (TM)!

I don't really want to manually re-input every new field into the database all over again, because it means I will have to re-design my database form all over again, too, and it would take me ages to get right. The incorrect case in the database design also reflects on the field names in the form, which is equally annoying.

So if any of you Microsoft Access gurus out there know a way around this problem, can they please let me know on this forum or even e-mail me at digistar4@gmail.com? I need the solution (if there is one) to be simple,, because I am not a programmer (IANAP) and I don't really want to get into writing macros or other computer program code. I wouldn’t call myself a beginner in Microsoft Access, but then again I am not an expert in this PC software, either.

Thanks very much in advance.

Alastair C Parker
0
PatHartmanCommented:
I'm not sure which setting corrects this so you may have to try one at a time.  Go to the Access Options dialog.  Click Options from the File menu for most new versions.  Older versions, I think it is on the tools menu.  Choose Proofing.  There are a bunch of settings that control autocorrect (don't forget to press the button to get additional autocorrect options).  One of these is probably the culprit.

PS - in the future - please start a new thread - also - if this doesn't solve your problem, start a new thread anyway and make a note here to respond in the new thread.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.