Finding Lower Case Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I need a query that will search in a field and output only those entries that have one or more lower case characters

Example

BENJ
BANJ
BENGA

I would want the query to output "BANJo", and not "BENJI" or "BENGAL".
 
The following function, which returns True if there are
any lower case characters will do it. Create a calculated
field in your query:

LC: ContainsLowerCase([yourfieldname])
and set its Criteria row to True

Function ContainsLowerCase(strCandidate As String) As
Boolean
On Error Resume Next
Dim inti As Integer
For I = 1 To Len(strCandidate)
If (Asc(Mid(strCandidate, I, 1)) >= 97 And _
Asc(Mid(strCandidate, I, 1)) <= 122) Then
ContainsLowerCase = True
Exit Function
End If
Next
ContainsLowerCase = False
End Function

Kevin Sprinkel
-----Original Message-----
Hello,

I need a query that will search in a field and output
Click to expand...
only those entries that have one or more lower case
characters.
 
I am using Access 97, so I'm not sure if that is the problem. I have tried what you were kind enough to post, but I'm no luck. Access doesn't seem to like the "Function" part

I get this error message: The expression you entered contains invalid syntax. You may have entered an operand without an operator

When I click on OK, the word "Function" is highlighted

Could you please elaborate a bit more with step by step instructions. I'm a bit of an Access novice

Thanks!!
 
Hello,

I need a query that will search in a field and output only those entries that have one or more lower case characters.

Example:

BENJI
BANJo
BENGAL

I would want the query to output "BANJo", and not "BENJI" or "BENGAL".
Click to expand...

Access searches are not case-sensitive and cannot be made so; what you
can do is to use the StrComp() function to find mismatches. A query
criterion of

StrComp([field], UCase([field]), 0) <> 0

will find those records where the value in field is not all upper
case.
 
What I left out was that you should define that function
in a standalone module. I forget sometimes that new users
haven't started using such things.

A new module is started by clicking the Module tab, and
choosing New. Any function or subroutine defined in such
a module is available throughout the application.

In this case, however, John Vinson's posted solution (not
surprisingly) is much more elegant.

Kevin Sprinkel
-----Original Message-----
I am using Access 97, so I'm not sure if that is the
Click to expand...
problem. I have tried what you were kind enough to post,
but I'm no luck. Access doesn't seem to like
the "Function" part.
I get this error message: The expression you entered
Click to expand...
contains invalid syntax. You may have entered an operand
without an operator.
When I click on OK, the word "Function" is highlighted.

Could you please elaborate a bit more with step by step
Click to expand...
instructions. I'm a bit of an Access novice.
 
Where
F1 is the field containing the data
Table2 is the table containing F1

SELECT Table2.F1, UCase([F1]) AS Expr1
FROM Table2
WHERE ((StrComp([F1],UCase([F1]),0)="0"));


--
Terry Kreft
MVP Microsoft Access


Xavier said:
Hello,

I need a query that will search in a field and output only those entries
Click to expand...
that have one or more lower case characters.
 
Back
Top