All CAPS in a table

[Guest]

Is there a setting within a table that will change text entered in lower case
to upper case?

 

[Rick Brandt]

Is there a setting within a table that will change text entered in
lower case to upper case?

You can use a format property of ">" and all text will *appear* in all caps.
If you want it to actually be stored in all caps you would need to run an
update query against all existing data...

UPDATE TableName
SET FieldName = UCase(FieldName)

....and then modify your data entry form so that all new entries are made in
all caps. That would be done either by using the BeforeUpdate event to
change all entries to upper case or by trapping keystrokes so that the
conversion is made as the user types (that's what I usually do).

 

[Sarah]

Kirk-
If on a field in a form, put this code in the field's AfterUpdate event.

NameOfField = UCase(NameOfField)



Sarah

 

[Guest]

Hi Rick

Would you care to share the code you use to convert to upper case as the
user types into a form. I currently use the before update event to convert it
all after it has been entered. but it strikes me that your way is a more
elegant solution.

Regards

Chris

 

[Rick Brandt]

Hi Rick

Would you care to share the code you use to convert to upper case as
the user types into a form. I currently use the before update event
to convert it all after it has been entered. but it strikes me that
your way is a more elegant solution.

In the KeyPress event...

KeyAscii = Asc(UCase(Chr(KeyAscii)))

If you want it to apply to the entire form use the KeyPress event of the
form and set KeyPreview to Yes. Otherwise you use the KeyPress event of the
desired controls.

 

[Guest]

Thanks Rick thats great.

Chris

In the KeyPress event...

KeyAscii = Asc(UCase(Chr(KeyAscii)))

If you want it to apply to the entire form use the KeyPress event of the
form and set KeyPreview to Yes. Otherwise you use the KeyPress event of the
desired controls.

 

[Guest]

Rick, This is what I need to do UPDATE TableName
SET State = UCase(State)

But in MS Access they use the "Build" Expression Builder feature and inside
of the build are Functions, constants and operators, etc. and inside of those
are dozens of other choices, so I need to know exactly how to perform the SET
or is set a key word or your logical expression. Or do I simple place this
SET as you noted, I inserted my field I call State. Also what does UCase$ do?

 

[tina]

what Rick gave you was the actual SQL statement. create a new query, but *do
not* add any tables to it. in design view, on the menu bar click View | SQL
View. the design view changes to a blank pane. delete the

SELECT;

that shows in the top left corner. then paste the SQL into the pane, as

UPDATE TableName
SET FieldName = UCase(FieldName)

replace TableName with the correct name of your table, and replace FieldName
with the correct name of your field.

UCase() is a built-in function the changes every alphabetic character in a
string to upper case.

btw, suggest you make a COPY of your database, and test the Update query on
the COPY. that way if you have problems with it, you still have the original
data safely untouched. always a good idea to play it safe, especially when
you're not familiar with action queries.

hth

 

[Guest]

In Access 2003, Fileld properties > default value.... is this where this is
entered? I did so and it did not work.

 

[Rick Brandt]

In Access 2003, Fileld properties > default value.... is this where
this is entered? I did so and it did not work.

No. It's VBA code that you enter in the VBA editor window for a particular
event. In this case the KeyPress event for the TextBox.