make all text uppercase... 1

[Junior1544]

I want to write some code that will on the click of a button in a form will make all text in a table upper case... right now there is a mix, and i would like to change the accual data to be upper case... i have about 7000 + records in the table...

i'm using access 2k... thanks...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[petermeachem]

UPDATE Table1
SET Field1 = ucase(Field1), Field2 = ucase(Field2), Field3 = ucase(Field3) Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[Junior1544]

how would i go about looping this through all fields, then all records in the database??

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[petermeachem]

You don't need to loop through all records, this does the lot. I assume you know in advance the field names, so just include them all in the query. You can make it very complicated if you want, just you don't have to. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[mflancour]

Here ya go.

Private Sub stuff()

Dim db As Database
Dim tbl As Table
Dim i As Integer
Dim j As Integer
Set db = CurrentDb()

For j = 0 To db.TableDefs("temptable1&quot.Fields().Count - 1

DoCmd.RunSQL "UPDATE tempTable1 SET tempTable1.[" & db.TableDefs("temptable1&quot.Fields(j).Name & "] = UCase([" & db.TableDefs("temptable1&quot.Fields(j).Name & "])"

Next j

End Sub

 

[WildHare]

Junior, could I suggest another route - go to the table DESIGN, and alter the FORMAT to > (a greater-than sign )

This will display the data in the field in UPPERCASE, regardless of how it's STORED.

Makes it a lot easier that way....


Jim
How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Junior1544]

Jim, but will that make it effect all my forms that are bound to the table, as well as the forms bound to queries bound to the table?? that's realy why i'm looking for it, so i don't have to make the property in every field in every form >... know what i mean??

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[WildHare]

Did you try it? Yes, setting the FORMAT of a field to > will cause it to DISPLAY that way in every form. I think... Not sure how it will handle this in a pre existing form - you might need to delete the field and then add it again to the form for it to know about the > format now. Or change the controls on the form, by selecting them all and adding > to the FORMAT property for the bunch of selected objects. I guess it's a question of HOW many fields on HOW many forms do you have, and whether you want to do a little work NOW, or a lot of work later on. Hindsight is always 20-20, as they say...

The only thing that looks a little odd sometimes is in a DATASHEET, as you go to change it, it might show the actual stored lowercase character. But it's no big deal. Certainly makes it easier than going around and changing every value. How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[mflancour]

Hehe, WildHare gets the star...Ya, sounds like this is all you need to do, as it will "effect all your forms that are bound to the table, as well as the forms bound to queries bound to the table", unless otherwise specified in the form/query.

the code I through in here will work fine, but it is only needed
1. if you actually need your data to be uppercase and not just formated that way.
2. if you have a ton of fields (columns) in your table and dont want to make an update query listing every field seperatly.