Compare capitals with non-capitals

[PayeDoc]

Hellow All

I have a table with several thousand records, where one of the text fields
'employee' sometimes is in capitals and sometimes not. Is it possible to
create a query that will return only those records where there are two or
more consecutive capitals?

Hope someone can help

Many thanks
Leslie Isaacs

 

[KARL DEWEY]

UNTESTED
Create a table named Alpha with field Alpha containing A through Z. Put
both tables in a query.
Use this calculated field --
MyCheck: Abs(InStr([YourField], [Alpha].[Alpha]) - InStr([YourField],
[Alpha].[Alpha]))
Use criteria of 1

 

[John Spencer MVP]

The only way I can think to do that is to construct a custom VBA function
since queries are not case-sensitive. Such a function might look like the
following untested function.

Public Function fTwoCaps(strIN) As Boolean
Dim i As Long
Dim tfReturn As Boolean
Dim X As Long, Y As Long

If Len(strIN & "") = 0 Then
tfReturn = False
Else
For i = 1 To Len(strIN) - 1
X = Asc(Mid(strIN, i, 1))
Y = Asc(Mid(strIN, i + 1, 1))
If X >= 65 And X <= 90 And Y >= 65 And Y <= 90 Then
tfReturn = True
Exit For
End If
Next i
End If

fTwoCaps = tfReturn

End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Clifford Bass]

Hi,

How about using the StrComp() function and compare the entire field?

StrComp(YourField, UCase$(YourField), 0)

When YourField is entirely capitals, the function will return 0,
otherwise it will return 1 or -1. Usually (maybe always) 1.

Clifford Bass

 

[Clifford Bass]

Hi Karl and John,

Remember to take into consideration letters outside of the standard
unaccented English A to Z.

Clifford Bass

 

[PayeDoc]

Hello Karl

Thanks for your reply.
I actually needed a function so that I could use for case-by-case
'validadtion' (as well as a global search, which your query would be ideal
for).
I have used John's suggested function.

Many thanks again
Les

UNTESTED
Create a table named Alpha with field Alpha containing A through Z. Put
both tables in a query.
Use this calculated field --
MyCheck: Abs(InStr([YourField], [Alpha].[Alpha]) - InStr([YourField],
[Alpha].[Alpha]))
Use criteria of 1

Hellow All

I have a table with several thousand records, where one of the text fields
'employee' sometimes is in capitals and sometimes not. Is it possible to
create a query that will return only those records where there are two or
more consecutive capitals?

Hope someone can help

Many thanks
Leslie Isaacs

 

[PayeDoc]

Hello John

That's perfect (and I even understand it!) - very many thanks.

Les

 

[PayeDoc]

Hello Clifford

Thanks for your reply.
I actually needed a function that would return records where part (but not
just the first letter) of the input is capitalised, not just the
all-capitalised values.
I have now used John's suggestion.

Thanks again though - your help was appreciated.
Les