Checking for upper/lower case text

[Lynn63]

I need to build a query that will check the name field for Uppercase. Rule is that the name field should be uppercase. We want to find all instances where there is lowercase in the name field. Any suggestions? Thanks...

 

[Mile-O]

Switch the query design to SQL view:

Copy and paste this:

UPDATE [MyTable] SET [MyField] = StrConv([MyField], 1);

Change MyTable to your table name;
Change MyField to your field name;


Run the query.

 

[Lynn63]

Thanks so much for your quick reply. My problem is a bit different than that. The "real" data actually resides in an ERP system (SAP). I will be receiving a download which I will upload into Access. Once in Access, I will be looking for all sorts of bad data, the upper/lower case issue is just one thing. I will need to identify which records have lowercase and then someone will need to go into SAP and fix it. Not very efficient, I realize, but this is the best we can do with the tools we have! So, is there a way I can simply check for the lowercase records? Thanks again.
--Lynn

 

[dcx693]

OK, decided just to take this one on as a challenge.

You can use the StrComp function like this:
StrComp(string1, string2,0) to see if they are the same. The 0 in the 3rd parameter slot tells it to do a "Database" compare - which means that it will differentiate between upper and lower case. Now, how to get a normal query to test out very character of a string? No need to. Just compare the Upper case version of the text to the text itself. If it's different, then it means there's a lowercase character in there somewhere.

I finally came up with a query expression. Place this into the "Field:" line of a blank column in your query design view. It will produce a column called Test.
Test: StrComp(UCase([your_string]),[your_string],0)=0
Then, in the Criteria line of that same column, put this:
False
Be sure to replace [your_string] with the actual field name from your table.

It should return all the cases where you've got lower case characters.

 

[HAFPT]

Thanks dcx693 this string compare was great, I just used it in a query, works like a charm. Had the same type of problem, I'm looking for data entry issues in our ERP system, Address Book module.

 

[carlnewboult]

dcx693 your a star had a urgent problem at work and with you help got it sorted hours before i had to have it done so was able to do nothing for the rest of the day

 

[Boanet]

Hi i have a different spin on this thread, my data has mixed upper and lower that i need to correct, any ideas? example JOHn sMIth... John Smith

 

[jdraw]

You have posted in thread almost 10 yrs old.
You can convert your data to Upper case using the UPPER() function.

YourDataInUpper = UCase(YourTextDataField)

Correction:
UCase is the proper function, not Upper as I had originally.
Spotted by Galaxiom.

If you need JOHn sMIth to be John Smith , then research properCase

There are limitations with some combinations:

eg:
McDonald
O'Leary
van Allen
de la Hoya etc.

 

[Galaxiom]

You have posted in thread almost 10 yrs old.
You can convert your data to Upper case using the UPPER() function.

Except the function is Ucase().

 

[jdraw]

Galaxiom,

You are absolutely correct. Upper is Oracle...hmmm.
I updated the post to correct the function to UCase.
Thanks.