auto capitalise first letter

[damaruk]

How do you get Access to auto format the capitalisation of the first letter of a word ie) in an address field in a form and can you get it to format say an incorrectly capitalised input mid word back to lower case

Cheers [sig][/sig]

 

[MichaelRed]

damaruk,

Check Ms Access help. Under "case", ther are several topics regarding correction of case (capitalization) errors on data entry. I am not aware of any function like "Proper" in Ms Access, however the function is trivial to generate - it is just difficult to use properly (no pun intended), since you need to "know" what to NOT capitalize in many cases.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[jgarnick]

Have you tried using an input mask in the address fields? Check Help--I think there are some examples of what you are looking for-- [sig]<p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br> [/sig]

 

[Lightning]

Check Help for the StrConv function. This has an option for Proper Case.

Lightning [sig][/sig]

 

[MichaelRed]

LIghtning,

Thanks for your reply. I have never run across this function - but have &quot;coded&quot; the Proper function a few times. Now I won't have to do it again - at least in Ms Access.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[damaruk]

How & where would you write the code for the &quot;Proper&quot; function? [sig][/sig]

 

[MichaelRed]

damaruk,

I think we have wandered a bit from your inquiry. Your original post refered to &quot;forcing&quot; the input on a form to follow your rules. In my opinion, the proper response to this is what I posted (much) earlier. The various &quot;Case&quot; constraints may be applied to the control (text box) where the data is input by the user. If you want to use the StrConv function refered to by Lightning, it would probably be used in the validate or after update functions of the individual control (text box) [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[damaruk]

I have lookied in the help files re StrConv as suggested. Unlive Access97 where there seemed to be a good explination and also examples of how to utilise the topic, Access 2000 that I have upgraded to seems to have a backward step in relation to help and basically tells you nothing. To be honest i am regretting moving to 2000 on a lot of functions but the rest of my collegues upgraded to I was in the minority (a bit like Tony Blair at the moment!) Could you therefore explain what this StrConv is and where and how I should utilise it.

Many thanks
Mark [sig][/sig]

 

[Guest_imported]

Try this in your queries or text box. It uppercases each word in the string
Song Lyricist: (StrConv([Lyricist],3))

I have a music data base and the original entries were all upper case so by using the StrConv i was able to show the information with only the first letter in upper case. This also works on information that is all lower case as well.

HTH

 

[MichaelRed]

damaruk,

the following examples are all done in the immediate window w/ Ms Access 97:

Mystr = &quot;michael l red&quot;
? StrConv(MyStr, vbProperCase)
Michael L Red
mystr = &quot;michael l red jr&quot;
? strconv(mystr, vbProperCase)
Michael L Red Jr
MyStr = &quot;michael l red iii&quot;
? strconv(Mystr, vbProperCase)
Michael L Red Iii

As you can see from the lase example, there can be some problems in using this approach. David D's example appears to be taken from a query (syntax), however the equivalent would work for a form control (text box) placed in the correct event.

You need to be a little careful. Placing it &quot;bare&quot; in an after update event will cause an endless loop, because each after update will cause an update, which will call the after update .... soon to run out of memory ...., so you would first need to check wheather the entry was already proper.

Also, the norm /default for Ms Access is &quot;option compare database&quot;, which defeats a direct comparision based on case sensitivity (AAAA = aaaa)? so your inital test needs to use an additional function - StrComp - Below is a sample which &quot;works&quot; in the after update event, with the problems noted above (e.g. changing iii to Iii) unresolved. Note that the list of exceptions in putting proper names in &quot;Proper&quot; format appears to be essientially infinite. In one previous lifetime, I maintained a modest database of ~~ 1000 records w/ &quot;Proper names&quot; in two or three fields. I did a &quot;roll my own version of the strconv - proper, which worked very much like it - the exceptions counted out to almost 100 seperate instances where &quot;Proper&quot; did not return the &quot;correct&quot; capitalization.

Private Sub txtPublisher_AfterUpdate()

If (StrComp(txtPublisher, StrConv(txtPublisher, vbProperCase), 0)) Then
txtPublisher = StrConv(txtPublisher, vbProperCase)
End If

End Sub
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]