Format text as Proper (Upper & Lower combined) 2

[JCG4LOM]

I want data that comes from my query in 'upper' format to display in 'proper' format in the report:

I.E. MR. GENE BOOGIEMAN
will be Mr. Gene Boogieman

I can do this in Excel and Word...


Please help. Thanks!

 

[SgtJarrow]

Set the control source for the field in question to:

=StrConv([textbox], vbProper)

****************************
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, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JCG4LOM]

Hello,

Thanks for the tip, but even though I can see how it would work I can't get it to work. I have tried this in 2 places:

In the query I made a new field

Proper: StrConv([txtField], vbProper)

As soon as I got out of the field it showed this:

Proper: StrConv([txtField], [vbProper])

I also tried it as the control source in a report field as

= StrConv([txtField].[tblName], vbProper) and also as
= StrConv([txtField].[tblName], "vbProper&quot

and it keeps throwing brackets around the vbProper.

Should I be doing this somewhere else?

Thanks!

 

[SgtJarrow]

sorry about that....vbProper should be vbProperCase

****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JCG4LOM]

Still no joy,

Both in the created field within the query and within the control source or format property fields of a report textbox Access is still adding on the brackets. Access really doesn't like it!

Any other ideas? Thanks for your help

 

[CosmoKramer]

Try:

Proper: StrConv([txtField], 3)

Hoc nomen meum verum non est.

 

[PaulBricker]

What version of Access are you using and have you looked up the StrConv() function to see what the correct constant (vbProperCase is a constant so you don't enclose it in ""'s)
is for your version.

Paul

 

[JCG4LOM]

To CosmoKramer-

It worked, you are a genious! Now for the super-challenge if you're up for it. I know this is a total longshot and has a 99% probability of being impossible, but here we go:

I've got a variety of names in this field, like the examples below:

Mr. Fred Rogers
Mrs. Smarty Pants, M.d.

See how the second letter is lower, although I'd like it to be upper? I can't change the data to be M._D. And I can't just add a rule to have the last letter upper... Any ideas?

I'm really pleased to have this tip, though, I appreciate your help, everyone.

Thanks!