Using the PCase function

Like with any custom function, you can use PCase() wherever you would use a built-in function. Look at an example where you have a large table of names and addresses, where everything is in uppercase, as in Figure 11-3. For the sake of example, call this table UglyCustomers (which isn't an insult to the customers — just the way their names are typed in!).

Now that you have a PCase() function that can convert text to proper case — without messing up the Mc's, Mac's and P.O.'s — you can use that in an update query to convert all the Text fields to proper case.

Figure 11-3:

Sample problem table in all uppercase.

JVXNG/

JVXNG/

Figure 11-3:

Sample problem table in all uppercase.

Test it on a copy of your original table first. That way, if you made any mistakes that mess up the data, you will have only ruined the copy of the table.

To create an update query to do the job, close the VBA editor to get back to Access. Then create a new query that uses the problem table as its source. Next, choose QueryOUpdate Query to convert the query to an update query. The Query-by-Example (QBE) grid gains an Update To column, in which you can add an expression that defines the new value for a field. Thus, you can add any Text field that needs converting to the QBE grid, and then use the expression =PCase([ fieldname]) (where fieldname is the same name as the field at the top of the column) to convert that field to proper case.

Figure 11-4 shows an example in which I'm fixing the FirstName, LastName, Company, and Address1 fields (as well as others that are just scrolled out of view). Notice that the Update To row for the FirstName field is PCase([FirstName]). The Update To row for the LastName field is PCase ([LastName]), and so forth. In other words, when the query runs, you want it to replace the contents of that field, in every record, to proper case.

Figure 11-4:

Query to fix uppercase problems in a table.

Figure 11-4 shows an example in which I'm fixing the FirstName, LastName, Company, and Address1 fields (as well as others that are just scrolled out of view). Notice that the Update To row for the FirstName field is PCase([FirstName]). The Update To row for the LastName field is PCase ([LastName]), and so forth. In other words, when the query runs, you want it to replace the contents of that field, in every record, to proper case.

The query shown in Figure 11-4 wouldn't work in a database that doesn't contain the PCase() function. It will work only in a database that has the PCase() function defined in one of its standard modules.

Because the query shown in Figure 11-4 is an action query, you need to run the query before it actually does anything.

1. Click the Run button in the toolbar or choose QueryORun from the Access toolbar.

You get the standard warning message (You are about to update x rows...).

2. Click Yes and wait a second. Then just close the query.

3. Back at the database window, click Tables, and then click the table that you changed.

If all went well, the fields will be in proper case. Figure 11-5 shows the results of running the sample query on the UglyCustomers table.

Figure 11-5:

Convert text fields by using the custom PCase() function.

_i uglyLustomers : lame

IHJti

eusflp-

F-irstRatne

UastName.

^■otripany 1 fcdfóííl

1

Ton

Pines

Arbor Claries 345 PacifipCoa'st Hwy

Sl

Marilou

Midcalf

500, gaíBth StrÍBti^w

Sl

3

Wllma

W&iftabe

Wannabe Whistles fei River Road, 3ürt9.23

Frankly

Unctuous

í34.N..Ra¡nbow.-Ür.

5

Marqaret

Anqstrom

P.O. BorfSgS

Marqle

McDonald

|l37QWáshinqton Lane

11

7

Abc Productions IIHaverstorr Styuäre

S

Hortense

Hiqqlefeottom

P.O: Boit'1014 11

9

Penny

MacQouqal

P 0. Box 10

10

Matilda

Stattfuck'

|323:S1nre Lane

11

JgSott And Wats

Scliumack

7'7'-¡ Ho h/^rin- Drive

12

Linda

Petersen.

823 Paseo Cancun

13

Ino

Yasha

17Ö8 Port Carlo Circle

14

Wiley Widqets 37 Roberts Dr.

15

Dominic

McFerrih'

45 Albari-r Risd

16

Rosemary

Stickler

1205 Huntingdon Ct.

I

17

Edmund

Kane

615 Levick Street

vl

1S

Mnnkhnnse K OsL-.-liff nf

| Record: 1 H <J | llllil lfUffUjlgT' < >

As you can see, the names and addresses in the fixed UglyCustomers table look a lot better than those in the original table. And the Mc and Mac last names — as well as the P.O. Box entries — look okay, too. Still, not everything is perfect. For example, Abc Productions probably should be ABC Productions. However, it would be pretty tough to write a function that deals with every conceivable exception to the standard use of uppercase letters in proper nouns. Some you might have to polish manually, but polishing off a few of them manually is a heck of a lot easier than retyping them all from scratch!

0 0

Post a comment