how to determine case of 1st letter

[arage]

i was trying to enforce referential integrity for my tables but was told that tblReviews has data that violates data integ. rules

while looking thru tblReviews i found some authorCodes that started with lowercase letters whereas tblAuthors has a field Code that starts with uppercase.

i'm basically wanting to write a select query that'll show me all authorCodes in tblReview that starts with a lowercase. I know that STRCONV will do conversion, but what function will tell me the status of a letter, whether the letter is uppercase or lowercase?

i just want to see these problem records with the lowercase letters before i go about updating them & seeing if that clears my data integ. problem

[This message has been edited by arage (edited 08-09-2001).]

 

[Fornatian]

Lookup Asc and Chr in VBA help. You can then write an iif statement to compare the string like:

Iif(Asc(Left([MyField,1))="A","Uppercase A","Not Uppercase A")

Each case has a corresponding Asc code that you can interrogate if you wish to do it like that.

Why don't you just run the query on the entire dataset because it won't do any harm if you strConv the already converted.

ian

 

[AlanS]

Another approach is to use one of these intrinsic functions: UCase, UCase$, LCase or LCase$. Each converts all letters within the argument to the indicated case, leaving non-letter characters unchanged. The ones ending with a "$" are for Strings, the others are for Variants.

For example, the following expression will evaluate to a Boolean value indicating whether or not the first character of MyField is lower case:

Left$(MyField, 1) = LCase$(Left$(MyField, 1))

 

[Atomic Shrimp]

Here's a function that will return True if a character (or the first character of a string) passed to is is upper case, otherwise False:

Public Function IsUcase(YourChar As String) As Boolean

YourChar = Left(YourChar, 1)
If Asc(LCase(YourChar)) <> Asc(YourChar) Then
    IsUcase = True
Else
    IsUcase = False
End If

End Function

 

[Atomic Shrimp]

But just out of interest; are you sure that the case difference is the problem? - I thought Access wasn't case sensitive for internal tables (although maybe it is when it comes down to enforcing referential integrity)

 

[arage]

i haven't figured that out yet Mike, whether or not internal case structure affects referential integrity.