Upper case field text formatting

[Hoja, Michael]

We have an Access Database that has fields which have been populated by
sloppy users. A typical example of one of the address fields says "13
abercorn street". This needs to be "13 Abercorn Street" but I need to make
similar changes to all record fields and the sort all fields so that the
first letter of every word is in Uppercase. Is there a way I can change
this automatically? This is so that we can do presentable mail merges.

Can someone help me on this please?

 

[Nikos Yannacopoulos]

I know this is not what you expected, but the easiest way to do this is
to copy your table into Excel, and use Excel's built-in function
Proper() on the desired columns, which does just what you want; then you
can copy the modified table from Excel and paste back into Access.

Whatever you do, always make a backup copy first!

HTH,
Nikos

 

[Geoffs]

Hi Michael,
Yes, there is a built in function for this "StrConv" which takes 2 arguments
- the string to be converted, and how you want it converted. So in your case
it will be :-

Dim strIN As String, strOUT As String
strIN = rst("addressfield")
strOUT = StrConv(strIN, vbProperCase)

If you highlight StrConv and press "F1" then help will show you the full
list of conversions that you can make with this function.

Geoffs

 

[Nikos Yannacopoulos]

StrConv(strIN, vbProperCase) is very cool, I admit I didn't know
constant vbProperCase (which does the same as function Proper in Excel,
which I proposed).

Actually it can be done in a plain update query with this function, no
VBA required! Just use:

StrConv([fieldname], 3)

in the Update To line in the grid in query design. 3 is the value of
vbProperCase, which isn't recognised per se in query design.

Nikos

 

[Lynn Trapp]

Actually it can be done in a plain update query with this function, no VBA

required! Just use:

StrConv([fieldname], 3)

Furthermore, there doesn't even have to be an update query. He can just call
the StrConv function as part of a select query for his report.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Hoja, Michael]

Thanks everyone for the help. I'll give that a go and let you know how I get on.

Michael

Actually it can be done in a plain update query with this function, no VBA
required! Just use:

StrConv([fieldname], 3)

Furthermore, there doesn't even have to be an update query. He can just call
the StrConv function as part of a select query for his report.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html