Change case in a Query

[lchiav]

I am tring to run a query to change the data in a field in a table from upper case to proper case and have been unsuccessful. In SQL view the update query looks like this -
UPDATE Prospects SET Prospects.LastName = StrConv([LastName],3);

When I try to run the query, I receive the following message:

"Undefined function "StrConv'in expression"

What am I doing wrong?
[sig][/sig]

 

[Robertd]

Hi lchiav,
i had that very same problem today with Ccur i gave up on it and resorted to the expresion builder and access was happy with that,... it might have been that i had something out of site in the query builder. Apart from the standard not so obvoius missing/unwanted space etc ;-)

UPDATE Customer SET Customer.LName = StrConv([LName],3);

as i should, it works fine, perhaps check the library references open a code module (it doesn't have to actually have code in it) and check that there arn't any references marked missing.

HTH
Robert

[sig][/sig]

 

[MichaelRed]

Not to discuss the syntax of the function.

BUT, you need to consider A LOT/ of issues/exceptions before you do it on a permanant basis.

MCFARLAND could be McFarland or Mcfarland - but 'proper' will make it the latter.

JONES III will become Jones Iii and obviously will be imPROPPER

I have dealt with a small database (approximatly 1000 member acssociation) which requested the mailing lists be converted to "Proper" case - I was up to ~ 75 "exception" cases when we 'gave up' and reverted to the UCase approach. There were even a few instances where the 'member' requested his/her entry be entirely different from the actual spelling of the real name!! Inspired, (according to hte member) by our "obvious" capability to place ANY thing in the member name field of the mailling list - and any other printed material using the names!!

It can get really messy!!

[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]