Upper/Lower Case 5

[Judalie]

I am importing data (names and addresses) into an Access database....the data is all caps and I want it to be converted to capitalize the first letter, then lower case for all of the data. I can use the input mask... >C<CCCCCCCC to do this for the first name and last name and city...however the street
address doesn't work... how can you force upper/lower case
on a street address.. i can only get it to be all lower case
please help..
thanks!
judy

 

[Judalie]

What I'm trying to ask...and maybe not doing it so well...is

how can you accommodate for the spaces in the street address?

 

[RickSpr]

I would suggest importing it as lower case, then running an Update query to convert it to proper name case (first letter of each word capitalized).

For the update query, you'll first have to create a function procedure in a standard module. The procedure header would be:
Public Function PCase(s As String) As String
The logic of the procedure should loop through the characters in the parameter s, converting the first character and the first character after a space to upper case. It returns the modified string.

In the update query, based on the imported table, drag the street address to the grid, and in its Update To: cell, enter &quot;PCase([street address])&quot;. Also enter &quot;Not Null&quot; in the criteria cell.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[SgtJarrow]

Judalie (and Rick),

Wouldn't it be easier to import the data as it stands and then just run an update query against the data and use:

StrConv([field], vbProperCase)

This will convert the first letter of every word to capitalize and lowercase everything else...

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[RickSpr]

Bob,

Thank you! For some reason I always forget about StrConv() having this ability. I always wind up looking for a PCase() function (analogous to UCase() and LCase()), which of course doesn't exist, so I fall back on writing it.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[judgehopkins]

When I try to use StrConv([field], vbProperCase) in an update query, I get an unrecognized expression error message.

What causes that?

I know that you cannot use a DAO object in a query.



&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[SgtJarrow]

judgehopkins....

Exactly how is the query setup?

If you are using the query builder....the field line should be the field you wish to update. The UpdateTo line should be the above posted code, replacing field with the name of the field.

This should execute fine....If you are still having errors, open your query in SQL mode and copy and paste the SQL statement here for us to take a look at.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[judgehopkins]

I think I'm double-threading, a capital offense in some jurisdictions.

So, for my part of this thread, go to: thread701-584237

Thanks!

(Robert, I believe I complied with your requests in that thread. Thanks for your time.)

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[MichaelintheUK]

Hello Judalie

Ty using 3 instead of vbPrpercase

1 = uppercase
2 = lowercase
3 = propercase

Field Street
Table tablemame
Updateto StrConv([Street],3)

Thanks

Michael

 

[judgehopkins]

MichaelintheUK and mstrmage1768,

When I use the code below, I get one of those irritating &quot;Enter parameter value&quot; dialog boxes.

What, instead, should I use?

Update OldData 
Set BuyerOwnerLN = StrConv([OldData],3)
;

&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air

 

[MichaelintheUK]

Hello The Judge

You are updating with the table name not the field name.

Update tablename set fieldname = StrConv([fieldname],3)

Therefore something similar to below

Update OldData
Set BuyerOwnerLN = StrConv([BuyerOwnerLN],3)


Thanks

Michael

 

[judgehopkins]

MichaelintheUK, you are, of course, correct. How do I do this with wild cards so I don't have to type in each field?

(Rant follows code.)

UPDATE OldData 
SET SellerFN = Trim(StrConv([SellerFN],3));

I am so embarrassed.



I wrote that first code with one hand on the keyboard while the other hand lay lovingly on &quot;Access SQL&quot; from Access 2000 Developer's Handbook, Volume 1: Desktop Edition by GETZ Ken, LITWIN Paul, GILBERT Mike.

No, I do not own stock in SYBEX....

BTW, Access needs to have something like &quot;field rules&quot; where you could make universal changes like this in a table without having to spend 72 hours on a forum....





&quot;The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?&quot; Anthony Burgess, A Mouthful of Air