CODEWITHSUNDEEP
ms-accessvba
Scotch
Scotch

Reputation: 3226

Function to restore proper/lower case at column level

All text field values in my database had been converted to upper case. I would like to restore, column by column, certain text entries to either lowercase or proper case.

The pseudocode that I have in mind is something like:

for each entry in column x of recordset y
entry = strconv(entry,3)
end

This would be easier I could simply click on my tables in design view and change a property, but I don't see anything like this.

Upvotes: 1

Views: 986

Answers (2)

Fionnuala
Fionnuala

Reputation: 91356

Try:

SELECT Table1.atext, 
       UCase(Left([atext],1)) & LCase(Mid([atext],2)) AS SConv
FROM Table1;

An UPDATE query BACKUP FIRST

UPDATE Table1 SET atext = UCase(Left([atext],1)) & LCase(Mid([atext],2)) 
WHERE AText Is Not Null

Note

In this case, it seems that there was a Format added to the table that forced upper case. For the most part, I reckon formats in tables should be avoided. They are more appropriate to queries and forms.

Upvotes: 3

iDevlop
iDevlop

Reputation: 25262

Step 1: there is no Proper() function in Access, but there is one in Excel. Therefore use it, by referencing Excel in the VBE, and creating a function: 

Function fProper(x As Variant)
    fProper = Excel.WorksheetFunction.proper(Nz(x))
End Function

Step 2: update your data by creating an update query similar to this one: 

UPDATE Clients SET CompanyName = fProper([companyname]);

Once that works, you can start coding to automate the "for each" part, if required.

Upvotes: 1

Related Questions