Help with new query

[RUDOLFSUN]

Hello,
My last quey was answered superbly, however I now need to know what I should do to find a word all in upper case in a field.

E.g.

Daves hardware inc
Daves hardware inc
Daves hardware inc
DAVES HARDWARE INC
Daves hardware inc
Daves hardware inc
Daves hardware inc
Daves hardware inc
JOS LARGE RUBBER THINGS
joys large rubber things
joys large rubber things
joys large rubber things
joys large rubber things
joys large rubber things

Now I need a quey that brings back just the values that are in upper case, the whole value and not just the first letter, so I would wnat JOYS LARGE RUBBER THINGS, and DAVES HARDWARE INC.

Any help would be great.

 

[The_Doc_Man]

There is a parameter you set in VBA sections and there is a corresponding parameter in the Options (Preferences?) that governs the type of comparison you do. You can choose to be case-sensitive or not. I'm guessing that at the moment you are NOT case-sensitive. But you could be if you wanted to be. Just a tick-box in the setup of the DB

 

[RUDOLFSUN]

ok

once I have ticked that box, how do I do a comparison?


also how do I expand this quey so I can bring back a sentence with all lower case characters


IIf(IsNull([YourTableFieldNameGoesHere]),1,Asc(Left([YourTableFieldNameGoesHereAlso],1)))


eg

dog house rock
Dog house Rock
dog House rock

I would want to bring back every instance

 

[DALeffler]

Easy enough (I think this'll work...). Your SQL will be:

SELECT YourTable.YourTableField, IIf(IsNull([YourTableField]),False,fstringcomp([YourTableField])) AS expr1
FROM YourTable
WHERE (((IIf(IsNull([YourTableField]),False,fstringcomp([YourTableField])))=True));

And the code in the module where function fStringComp resides will look like:

Option Compare Binary

Function fStringComp(sPassed As String) As Boolean

fStringComp = False

If UCase(sPassed) = sPassed Then
    fStringComp = True
    Exit Function
End If

While UCase(Left(sPassed, 1)) = Left(sPassed, 1)
    If InStr(1, sPassed, " ") Then
        sPassed = Mid(sPassed, InStr(1, sPassed, " ") + 1)
    Else
        Exit Function
    End If
Wend

fStringComp = True

End Function

 

[RUDOLFSUN]

Hi I copied and pasted it into sql, and renamed the values where needed. however I got an error saying undefined function fstringcomp


what am I doing wrong, my sql looks like this

SELECT IIf(IsNull([FST_NAME]),False,fstringcomp([FST_NAME])) AS expr1
FROM Siebel_mkt_contact_12012006_EMILE_HAS
WHERE (((IIf(IsNull([FST_NAME]),False,fstringcomp([FST_NAME])))=True));

 

[Bat17]

perhaps simpler:

for all upper
WHERE (((StrComp(UCase([FST_NAME]),[FST_NAME],0))=0));

for all lower
WHERE (((StrComp(LCase([FST_NAME]),[FST_NAME],0))=0));


HTH

Peter

 

[DALeffler]

Try This:

SELECT Siebel_mkt_contact_12012006_EMILE_HAS.FST_NAME
FROM Siebel_mkt_contact_12012006_EMILE_HAS
WHERE (((IIf(IsNull([FST_NAME]),False,IIf(StrComp(StrConv([FST_NAME],3),[FST_NAME],0),True,False)))=True));