Re: Find uppercase letter in a string


"Arts" wrote:
I get an syntax error when I am using Strcomp. (on the first comma)
How can I get 2 rows where there is two uppercase letters in the same
field
like BiiiD2ii? The result should be B in one row an D in the second row.
--
There may be simpler methods, but the only
thing I can think of to count upper case letters
in a string field is to write a function.

Something like:

Public Function fCntUCase(pStr As Variant) As Long
On Error GoTo Err_fCntUCase
Dim sTest As String
Dim i As Integer

If Len(Trim(pStr & "")) > 0 Then
sTest = pStr
For i = 65 To 90 '"A" to "Z"
sTest = Replace(sTest, Chr(i), "", 1, -1, vbBinaryCompare)
Next i
fCntUCase = Len(pStr) - Len(sTest)
Else
fCntUCase = 0
End If
Exit_fCntUCase:
Exit Function

Err_fCntUCase:
MsgBox Err.Description
Resume Exit_fCntUCase
End Function


?fcntucase("BiiiD2ii")
2
?fcntucase("Biii(aii)")
1
?fcntucase(null)
0

Once you have a count, you can join this count to
a simple number table where "2" is repeated twice
but all other possible counts occurs once....

tblNum
Num
0
1
2
2
3
4
5
6
7

The two 2's will cause a record with count of 2
to be repeated, but none of others.

SELECT *,
fCntUCase([KRITERIUM]) As Cnt,
FROM
yurtable
INNER JOIN
tblNum
ON
fCntUCase(yurtable.KRITERIUM) = tblNum.Num;

It probably won't be a snappy query depending
on how much data you have. You could save
query that computes count to a temp table,
then join temp table to tblNum to get your
2 records for every record whose field has
2 upper case letters.

I assume this is a one-shot deal where you
are correcting errors in your table structure.
Otherwise, you are making this field "store"
2 "sets of information,"

1) the string itself
2) number of upper case chars

which will always get you in trouble....


.

Relevant Pages

  • Re: Find uppercase letter in a string
    ... How can I get 2 rows where there is two uppercase letters in the same ... thing I can think of to count upper case letters ... in a string field is to write a function. ...
    (microsoft.public.access.queries)
  • Re: Keeping only capital letters
    ... I am trying to write a recursive function that that takes in a ... letters by using recursion. ... I know that the function isstrprop determine whether string is of ... specified category, and I can specify upper case, but I don't know ...
    (comp.soft-sys.matlab)
  • Keeping only capital letters
    ... I am trying to write a recursive function that that takes in a string ( ... of alpha, sytax, lower and upper case letters) and returns only capital ... letters by using recursion. ...
    (comp.soft-sys.matlab)
  • Re: Accesing the container objects memebers from the contained object
    ... Sure you haven't mistyped parent? ... The string should include LQ (upper case letters, ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Some interesting facts
    ... Upper and lower case letters are named 'upper' and 'lower' because ... Well I won't be writing any poems tonight. ... of celery than the celery has in it to begin with. ...
    (alt.vacation.las-vegas)