Make a Field name Upper case on an Access table

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

I am refering to the information entered into the field. I forgot to add the valid possibilities are K,P or B.

You can also use strconv(), but UCASE is simpler.

Jim.

@Jim Dettman

I'm familiar with the UCASE on a form but where exactly on the field in the table do I put it?

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.

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.

afaik, None

< I had all those considered and was rejected by the IT team>

tell them to hit Caps Lock before entering data..

<<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.

@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.

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.

@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.

<<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.

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.

@hnasr
Thank you. This works. That's exactly what I needed.

Welcome!

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

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.