Last Name in capital

[gsrajan]

I have a text field which has last name and the first name of the customer. I wish to change the last name in capital and the firist name with the first letter in capital ( Example: JOHN, Doe ) with a comma. Please let me know the sql for this.



Thanks for your help.

Rajan.

[ItsMe]

Perhaps the StrConv() function with vbProperCase is what you are after.

[gsrajan]

Yes, since both are in the same text box, I do not know how to do.

Thanks for your reply

[gsrajan]

In short, whatever is in the left side of the comma, in capital and right side in vbpropercase.

thanks,

Rajan,

[ItsMe]

It will probably be best handled using VBA behind a form. You will need to get familiar with other functions too. Mid(), Left(), and perhaps instrConv()

[lfpm062010]

ItsMe is right.

But you can use those functions in a query as well. It just make the query looks complicated.

This should do the job.

UCase(Mid([Table1].[FullName],1,InStr([Table1].[FullName],",")-1)) & ", " & StrConv(Trim(Mid([Table1].[FullName],InStr([Table1].[FullName],",")+1)),3)

[gsrajan]

Thank you very much. I will try this.

Rajan.

[ssanfu]

For the record, one field for a name is a bad design.... breaks the rules of normalization.

If you had a "FirstName" field and a "LastName" field, converting the last name to uppercase would be trivial.

[gsrajan]

Thank you. But the existing database was created like this since the data was imported from excel in that format. I need to go with this.

Thanks again.