A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Upper case field text formatting



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2005, 09:44 AM
Hoja, Michael
external usenet poster
 
Posts: n/a
Default Upper case field text formatting

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?


  #2  
Old March 4th, 2005, 09:58 AM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

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

Hoja, Michael wrote:
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?


  #3  
Old March 4th, 2005, 10:03 AM
Geoffs
external usenet poster
 
Posts: n/a
Default

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 :-)


"Hoja, Michael" wrote:

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?



  #4  
Old March 4th, 2005, 10:21 AM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

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

Geoffs wrote:
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 :-)


"Hoja, Michael" wrote:


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?



  #5  
Old March 4th, 2005, 01:37 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

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


  #6  
Old March 13th, 2005, 03:39 PM
Hoja, Michael
external usenet poster
 
Posts: n/a
Default

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

Michael

"Lynn Trapp" wrote in message ...
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion SVC Worksheet Functions 9 February 28th, 2005 03:29 PM
Prevent Blank Records being written. Need Help. Robert Nusz @ DPS Using Forms 4 December 29th, 2004 06:15 PM
Concatenatd fields in a query for a searching form Marc Running & Setting Up Queries 8 October 19th, 2004 08:49 PM
check writing code funkmaster22 Using Forms 2 August 11th, 2004 09:35 PM
Is it possible to filter based on ceel format ludahen Worksheet Functions 4 June 9th, 2004 09:19 PM


All times are GMT +1. The time now is 08:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.