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 and 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!).

Figure 11-3:

Sample problem table in all uppercase.

Figure 11-3:

Sample problem table in all uppercase.

-

UglyCustomers

CustlD

» FirstName

LastName -

Company

Address

City •

l|TORI

PINES

ARBOR CLASSICS

345 PACIFIC COAST HWY

ESCON DIDO

2 MAR 1 LOU

MIDCALF

500, 999-6TH STREET SW

EDMONTON

3 WILMA

WANNABE

WANNABE WHISTLES

1121 RIVER ROAD, SUITE 23

CORNBALL

4 FRANKLY

UNCTUOUS

734 N. RAINBOW DR.

STATEN ISLANC

5 MARGARET

ANGSTROM

P.O. BOX 1295

DA NEVILLE

6 MARGIE

MCDONALD

1370 WASHINGTON LANE

BUCKINGHAM

8 HORTENSE

HIGGLEBOTTOM

ABC PRODUCTIONS

P.O. BOX 1014

ESCON DIDO

9 PENNY

MACDOUGAL

P.O. BOX 10

NEWHOPE

10 MATILDA

¡STARBUCK

323 SHIRE LANE

SKEEDADLE

11 SCOTT AN DN/ SCHUMACK

228HOLLYWOOD DRIVE

HOLLYWOOD

12 LINDA

PETERSON

823PASEO CANCUN

REDMOND

13! INO

YASHA

1788 PORT CARLO CIRCLE

FRAMINGTON

15 DOMINIC

MCFERRIN

45 ALBANY ROAD

MARITIME

16 ROSEMARY

STICKLER

1205 HUNTINGDON CT.

WILLOW GROV

17 EDMUND

KANE

615 LEVI CK STREET

PINE VALLEY

| Record! M

l°f33 ► H

K ^ No Filter |

-J

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

Test your function on a copy of your original table first. That way, if you make any mistakes that mess up the data, only the copy of the table gets ruined.

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, in the (Query Tools) Design tab, choose Update from the Query Type group 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 you can 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 the proper case.

Figure 11-4 shows an example in which we're fixing the FirstName, LastName, Company, Address, and City fields. 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 change the contents of that field, in every record, to the proper case.

Figure 11-4:

Query to fix uppercase problems in a table.

Figure 11-4 shows an example in which we're fixing the FirstName, LastName, Company, Address, and City fields. 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 change the contents of that field, in every record, to the proper case.

The query shown in Figure 11-4 wouldn't work in a database that doesn't contain the PCase() function. It works 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 can do anything. Follow these steps:

1. Click the Run button in the Results group of the (Query Tools) Design tab.

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

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

3. Back in the Navigation pane, click Tables, and then click the table that you changed.

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

Figure 11-5:

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

g UglyCustomers CustlD - FirstName 1 Tori

2 Marilou 3; Wilma

4 Frankly

5 Margaret

6 Margie S Hortense 9 Penny

10 Matilda

11 Scott And Nati Schumack

12 Linda Peterson 131 no Yasha

McFerrin Stickler

LastName Pines Midcalf Wannabe Unctuous Angstrom McDonald Higglebottorr MacDougal Starbuck

15 Dominic

16 Rosemary

Company Arbor CI assies

Wannabe Whistle;

Abe Production;

Kani

Address 345 Pacific Coast Hwy 500, 999-6th Street Sw 1121 River Road, Suite 23 734 N. Rainbow Dr. P.O. Box 1295 1370 Washington Lane P.O. Box 1014 P.O. Box 10 323 Shi re Lane 228 Hollywood Drive 823 Paseo Cancun 17SS Port Carlo Circle 45Albany Road 1205 Huntingdon Ct. 615 Levi ck Street

-1 City Escondido Edmonton Cornball Staten Island Daneville Buckingham Escondido New Hope Skeedadle Hollywood Redmond Framington Maritime Willow Grove Pine Valley

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. You might have to polish some fields manually, but editing a few of them is a heck of a lot easier than retyping them all!

Was this article helpful?

0 0

Post a comment