Re: Find uppercase letter in a string

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:


"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: 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)
  • 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 ... Dim sTest As String ...
    (microsoft.public.access.queries)
  • 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: adding a text string to data from one field in one database to another
    ... function that blocks anything but numbers or letters. ... Dim stAllowedChars as string ... dim stFrom as string stTo as string ... dim iPtr as integer. ...
    (comp.databases.ms-access)
  • Re: Format text not all caps
    ... PROPER - Straight out of *Help on this function* when you look at the function ... Converts all other letters to ... =PROPERProper case of first string ... The excel file that I ...
    (microsoft.public.excel)