Compare capitals with non-capitals

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

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


"PayeDoc" wrote:

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

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

PayeDoc wrote:
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

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

"PayeDoc" wrote:

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

Hi Karl and John,

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

Clifford Bass

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





"KARL DEWEY" wrote in message
...
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


"PayeDoc" wrote:

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

Hello John

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

Les


"John Spencer MVP" wrote in message
...
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

PayeDoc wrote:
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

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



"Clifford Bass" wrote in message
...
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

"PayeDoc" wrote:

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