Identifying lowercase characters in a field 1

[meagain]

Hi,

I need to search a field in a table and locate/identify any records containing lowercase characters. I will be making a table of these records.

If you know how I can do that, I would greatly appreciate your help.

Using Access 2002.

Thanks,
LP

 

[dhookom]

You might need to loop through the characters of the field and check for ASC(...) between 97 and 122.

If you want more specific help, please provide table and field names as well as sample records and results.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Aside from identifying lowercase characters in a field, would you mind telling us why you want to make a table of these records? Are you going to COPY the records? (bad idea, IMHO) Or MOVING them into another table? Would it be easier to just mark the records with the lowercase characters by using another field in the table? No copying / moving records required.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[cheerio]

Instead of looping through character by character you could define a VBA function as follows:

Public Function HasLowerCase(strTest As String) As String
    If (0 = InStr(1, strTest, UCase(strTest), vbBinaryCompare)) Then
        HasLowerCase = "Y"
    Else
        HasLowerCase = "N"
    End If
End Function

Then in your query add a column HasLowerCase([testfield]) with a criterion of "Y" where testified is the name of the field to be tested.
Using the InStr function should be faster than a VBA loop.

 

[Blorf]

You can do this with a query.

SELECT StrComp(UCase([MyField]),[MyField],0) AS test FROM MyTable WHERE (((StrComp(UCase([MyField]),[MyField],0))=True));

Using an = to compare the two won't work, but the Strcomp function does throw a true if the two text values don't match case sensitive.


There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

http://www.thelegacyreturns.com/

 

[meagain]

Thanks for your input, I went with BLORF's solution. Worked like a charm thank you.