CODEWITHSUNDEEP
ms-accessms-officecapitalize
Umm E Habiba Siddiqui
Umm E Habiba Siddiqui

Reputation: 594

Capitalize Just the last Letter in string - MS Access

I have a column in my access database table, I ran a query to make it proper case by using StrConv([MyColumn],3) but last two letters are state names and this query makes SOmeThing, soMethINg, NY to Something, Something, Ny, I want the result as Something, Something, NY Is there a another query I can run after to capitalize last letter?

Upvotes: 0

Views: 198

Answers (2)

abraxascarab
abraxascarab

Reputation: 701

Well, most people would tell you to store your 'address', 'city', and 'state' as separate fields. Then you Proper Case each separately and concatenate them together. If you can do that... that is your best approach.

If this is a database or file that's been tossed at you and you can't make the field/table changes... it's still possible to get your desired results. However, you better make sure all strings end with your state code. Also make sure you don't have foreign addresses since Canadian (and other countries) use more that two letters for the province code at the end.

But if you are sure all records contain two letter state abbreviations, you can continue with the following:

MyColumnAdj: StrConv(Mid([MyColumn],1,len([MyColumn])-2),3) + StrConv(right([MyColumn],2),1)

This takes the midstring of your [MyColumn] from position 1 to the length of your [MyColumn] minus 2 (leaving off the state code) and it Proper Case's it all.

It then concatenates (using the plus sign) to a rightstring of [MyColumn] for a length of 2 and Upper Case's it.

Once again, this is dangerous if the field doesn't have the State Code consistently at the end of the string.

Best of luck. Hope this helps. :)

Upvotes: 1

Gustav
Gustav

Reputation: 55981

You can use:

UcaseLast: Left([YourColumn], Len([YourColumn]) - 1) & UCase(Right([YourColumn], 1))

Upvotes: 2

Related Questions