Find uppercase letter in a string

[Guest]

I have field KRITERIUM containing strings for example Biii(aii) or Biii(D1)
an so on. I want to select all rows that contains uppercase letters anywhere
in the string. I want to get 2 rows where there is 2 uppercase letters. Is it
possible?
I have tried StrComp and Mid but I don't understand how I can get any
uppercase letter in the string(the first or the fifth or...)

 

[Gary Walter]

I have field KRITERIUM containing strings for example Biii(aii) or Biii(D1)
an so on. I want to select all rows that contains uppercase letters
anywhere
in the string. I want to get 2 rows where there is 2 uppercase letters. Is
it
possible?
I have tried StrComp and Mid but I don't understand how I can get any
uppercase letter in the string(the first or the fifth or...)
--

I would think StrComp would get the job done...

if the job is to identify rows that contain uppercase
letters anywhere in the string....

Maybe I have missed something, but what are your
results if you add the following to a Field row in an
empty column of your query design grid?

TestUpper: StrComp([KRITERIUM], LCase([KRITERIUM]),0)

it should be doing a binary compare of the field
with the lower case form of itself...

if they are equal, it should return 0
(meaning no uppercase letters)

if different, it should return either 1 or -1
(meaning there must be an uppercase letter)

if field is NULL, will return Null
(will assume you don't care about Null)

so, in Criteria row

<> 0

or, you can complete expression in Field row

HasUpper: StrComp([KRITERIUM], LCase([KRITERIUM]),0) <>0

then set Criteria to -1 or True

 

[Guest]

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.
--
Thank you.


Gary Walter skrev:

I have field KRITERIUM containing strings for example Biii(aii) or Biii(D1)
an so on. I want to select all rows that contains uppercase letters
anywhere
in the string. I want to get 2 rows where there is 2 uppercase letters. Is
it
possible?
I have tried StrComp and Mid but I don't understand how I can get any
uppercase letter in the string(the first or the fifth or...)
--

I would think StrComp would get the job done...

if the job is to identify rows that contain uppercase
letters anywhere in the string....

Maybe I have missed something, but what are your
results if you add the following to a Field row in an
empty column of your query design grid?

TestUpper: StrComp([KRITERIUM], LCase([KRITERIUM]),0)

it should be doing a binary compare of the field
with the lower case form of itself...

if they are equal, it should return 0
(meaning no uppercase letters)

if different, it should return either 1 or -1
(meaning there must be an uppercase letter)

if field is NULL, will return Null
(will assume you don't care about Null)

so, in Criteria row

<> 0

or, you can complete expression in Field row

HasUpper: StrComp([KRITERIUM], LCase([KRITERIUM]),0) <>0

then set Criteria to -1 or True

 

[Gary Walter]

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....

 

[Guest]

I used 2 queries and a new table and it works!
First I find the position for the letter "D" in the string and put it in a
new table:

INSERT INTO dbo_K_Art_Kriterier ( ArtsID, BokstavPosisjon, RodlisteKriterier )
SELECT DISTINCT dbo_t_DT_Rødlistevurdering.ArtsID,
(InStr(1,[Kriterier],"D",0)) AS Uttr1, dbo_t_DT_Rødlistevurdering.Kriterier
FROM dbo_t_DT_Rødlistevurdering
WHERE (((dbo_t_DT_Rødlistevurdering.Kriterier)<>"") AND
((InStr(1,[Kriterier],"D",0))>0));

Then I select the letter at this position and put it in a new column:
UPDATE dbo_K_Art_Kriterier SET BokstavKriterie =
Mid(RodlisteKriterier,BokstavPosisjon,1);


--
Thank you.


Gary Walter skrev:

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....