Problem after slash with strConv for upper case

[lorirobn]

Hi,

I am busy posting questions to this forum today! I've got one more, for now.

I use a strConv function to capitalize the first letter of each word in a text field on my form. It works fine, but I have one scenario where it changes an upper case to a lower case. The scenario is when I type in: "Towel Shelf/Bar Unit". It actually changes the 'B' in Bar to lower case, and ends up being "Towel Shelf/bar Unit". This is not a major deal but just drives me batty because it doesn't look right. Is there any way around it?

The VBA statement is:

    ItemSubcatDesc = StrConv(ItemSubcatDesc, 3)

Thanks in advance...
Lori

 

[lorirobn]

ps - the other thing that happens is when I have "PB Bathroom", it changes it to "Pb Bathroom". Is there any way to capitalize the first letter of each word and NOT change anything else?

 

[PHV]

If the standard ProperCase function provided by Microsoft doesn't suit your goal, I'm afraid you have to write your own function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Both questions are not possible, using just one VBA function.
You will have to create your own, incorporating several Built in Functions,
to achieve your desirder results.

eg;

x = InStr(ItemSubcatDesc,"/")
If x > 0 Then
ItemSubcatDesc = Replace(ItemSubcatDesc,"/", " / ")
ItemSubcatDesc = strConv(ItemSubcatDesc ,vbProperCase)
ItemSubcatDesc = Replace(ItemSubcatDesc," / ", "/")

etc...

typed, not tried

 

[lorirobn]

ohhhh, I see. Thanks for the suggestion, Zion7; I will play around with that.

 

[lorirobn]

Zion7: I did exactly as you said and it works like a charm, thank you. It helps to have ideas on how to work with VBA above and beyond the general functions.

 

[lorirobn]

I would like to write my own function to capitalize the first letter of each word, and NOT convert to lower-case everything else (which is what I am discovering that strconv does).

I am somewhat a newbie to Access/VBA, but can figure out some of it...

I would search the string for a 'space and letter' combination, and that would mean it is a new word. I would then figure out what position the letter is, and then just do strconv on that position? Repeat for the next 'space and letter' combination. And also do the first character of the string.

Does this sound correct?

Thanks...
Lori

 

[Zion7]

Yes, it does, off hand.
If you can predict what sort of strings, you'll be up against, you can with confidence assume that just looking
for a space, will suffice?

...prepare yourself, for some tedious logic.

Depending on the length of your string,
you may want to loop through each character,
and asess accordingly.

For x = 1 to Len(ItemSubcatDesc)
If Mid(ItemSubcatDesc,x,1) = " " Then