Compare capitals with non-capitals

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

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
 
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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
Back
Top