SQL query: check for uppercase (UCASE?) in a value

[Thomas McK]

Hi all,

I'm trying to make a SQL query (against a table in MS Access) where I
want to take the value of something, and check to see if the first two
letters in that value are capitalized (for importing purposes later
on!). For example:

re_200_3 <-- I want this to be returned because the "re" isn't
uppercase.
RE_200_2 <-- This value would be fine because it *is* uppercase.

So, my question, would I be able to treat the value as a String in
this query and return the value that does not have just the first two
letters uppercase? I was thinking about possibly using a TRIM
function to possibly drop everything after the '_', but I'm not sure
that's possible. (If only I was doing this in VB I could create an
array...etc...but c'est la vie). I've done a bit of research and seen
people use functions such as UCASE and CHECK, but for some reason I
doubt this will work in my case. Is this possible using only SQL?
Any thoughts?

ps,
table name is: tblMyID
column name is: RE_ID

Thanks in advance!
Tom

[Trev]

Thomas McK wrote:

Hi all,

I'm trying to make a SQL query (against a table in MS Access) where I
want to take the value of something, and check to see if the first two
letters in that value are capitalized (for importing purposes later
on!). For example:

re_200_3 <-- I want this to be returned because the "re" isn't
uppercase.
RE_200_2 <-- This value would be fine because it *is* uppercase.

So, my question, would I be able to treat the value as a String in
this query and return the value that does not have just the first two
letters uppercase? I was thinking about possibly using a TRIM
function to possibly drop everything after the '_', but I'm not sure
that's possible. (If only I was doing this in VB I could create an
array...etc...but c'est la vie). I've done a bit of research and seen
people use functions such as UCASE and CHECK, but for some reason I
doubt this will work in my case. Is this possible using only SQL?
Any thoughts?

ps,
table name is: tblMyID
column name is: RE_ID

Thanks in advance!
Tom

IS your database set to be case senstive? If so then ">='a'"

If not then
Where (ASC(Left(FieldName,1)) between 97 and 122 ) And
ASC(Mid(FieldName,2,1)) between 97 and 122

Will return "re" but not "Re" or "rE", to get them use "or" instead of
"and".

[Eric Schittlipz]

"Thomas McK" <ed********@yahoo.com> wrote in message
news:85**************************@posting.google.c om...

Hi all,

I'm trying to make a SQL query (against a table in MS Access) where I
want to take the value of something, and check to see if the first two
letters in that value are capitalized (for importing purposes later
on!). For example:

re_200_3 <-- I want this to be returned because the "re" isn't
uppercase.
RE_200_2 <-- This value would be fine because it *is* uppercase.

So, my question, would I be able to treat the value as a String in
this query and return the value that does not have just the first two
letters uppercase? I was thinking about possibly using a TRIM
function to possibly drop everything after the '_', but I'm not sure
that's possible. (If only I was doing this in VB I could create an
array...etc...but c'est la vie). I've done a bit of research and seen
people use functions such as UCASE and CHECK, but for some reason I
doubt this will work in my case. Is this possible using only SQL?
Any thoughts?

ps,
table name is: tblMyID
column name is: RE_ID

Thanks in advance!
Tom

Look at the strComp function in the help file. In the example below I have
defined an extra column for a query and where the first 2 characters are in
uppercase, the column returns 0:

MyResult:StrComp(Left([MyField],2),UCase(Left([MyField],2)),0)