Update Query to change lower case to upper case

[Guest]

Is there a method to create an update query that will change any values of a
primary key field to upper case from lower case. I have mixed values. Some
are in upper case and some are in lower case. I need them all to be in upper
case. I use this database to populate a DB2 database which does care about
case. Also, is there a way to make the value of this field when input,
change to upper case? I would like the application to change any input on
this field to upper case automatically. Thanks in advance for your help.
Matt

 

[Rick B]

I believe you would build an update query and use something like...

UCase([YourFieldName])

 

[Guest]

You can use the Ucase function to update your values; however, as they are
primary keys, are you going to break any relationships? In other words, do
these records have any records in other tabled related to them?

 

[John Vinson]

Is there a method to create an update query that will change any values of a
primary key field to upper case from lower case. I have mixed values. Some
are in upper case and some are in lower case. I need them all to be in upper
case. I use this database to populate a DB2 database which does care about
case. Also, is there a way to make the value of this field when input,
change to upper case? I would like the application to change any input on
this field to upper case automatically. Thanks in advance for your help.
Matt

Run an Update query updating the field (which I'll call MyField) to

UCase([MyField])

Be sure to use the square brackets or you'll update all records to the
text string MYFIELD - and *do* make a database backup first, just in
case!

You can force all input to this field to upper case by ensuring that
all input is done through a Form (table datasheets have no usable
events). Use the AfterUpdate event of the textbox bound to this field:

Private Sub MyField_AfterUpdate()
Me!MyField = UCase(Me!MyField)
End Sub

John W. Vinson[MVP]

 

[Guest]

No, there is only one table. In the DB2 table, this field is not the primary
key. I tried what Rick B mentioned, but I'm curious if it makes a difference
that some values are numeric and some are alphanumeric? I got an error about
type conversion failure when running the update query. Any suggestions?
Thanks.

 

[Guest]

Thank you John. That worked perfectly. Much appreciation for your help.

Is there a method to create an update query that will change any values of a
primary key field to upper case from lower case. I have mixed values. Some
are in upper case and some are in lower case. I need them all to be in upper
case. I use this database to populate a DB2 database which does care about
case. Also, is there a way to make the value of this field when input,
change to upper case? I would like the application to change any input on
this field to upper case automatically. Thanks in advance for your help.
Matt

Run an Update query updating the field (which I'll call MyField) to

UCase([MyField])

Be sure to use the square brackets or you'll update all records to the
text string MYFIELD - and *do* make a database backup first, just in
case!

You can force all input to this field to upper case by ensuring that
all input is done through a Form (table datasheets have no usable
events). Use the AfterUpdate event of the textbox bound to this field:

Private Sub MyField_AfterUpdate()
Me!MyField = UCase(Me!MyField)
End Sub

John W. Vinson[MVP]