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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |