MS Access select query to find all fields that begin with lowercase letter

I have memo fields populated with text.
some begin with an uppercase letter, others do not.
i need 2 select queries:
1) select all fields that begin with a LOWERCASE letter
2) select all fields that DO NOT begin with an UPPERCASE letter

how can i build these two queries?
gleveratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Begin with Lower Case:

SELECT Table1.FIELD1
FROM Table1
WHERE (((Table1.FIELD1) Is Not Null) AND ((Asc([FIELD1]) Between 97 And 122)=True));

DO NOT begin with an UPPERCASE letter

SELECT Table1.FIELD1
FROM Table1
WHERE (((Table1.FIELD1) Is Not Null) AND ((Asc([FIELD1]) Not Between 65 And 90)=True));

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This mod takes into account Null in the field:

1)
SELECT Table1.FIELD1
FROM Table1
WHERE (((Table1.FIELD1) Is Not Null) AND ((Asc(Nz([FIELD1],0)) Between 97 And 122)=True));

2)
SELECT Table1.FIELD1
FROM Table1
WHERE (((Table1.FIELD1) Is Not Null) AND ((Asc(Nz([FIELD1],0)) Not Between 65 And 90)=True));

mx
0
gleveratorAuthor Commented:
i am getting an error with each of these.
for first solution, i am getting this error:
data type mismatch in criteria expression

for second solution, i am getting this error:
invalid procedure call

please note it is a memo field. not sure if that matters or not.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
No points please!

Or you could use:

WHERE Asc([FIELD1] & " ") Between 97 And 122

and

WHERE Asc([FIELD1] & " ") NOT Between 65 And 90



0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
gleverator: - see my 2nd set  of posts, which handle that problem

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"for second solution, i am getting this error:
invalid procedure call"

It works for me ... humm
0
Dale FyeCommented:
gleverator:

Try the WHERE clause I used in my example.  I basically took what Joe (mx) did and shortened the WHERE clause.  But instead of using the NZ function, I just appended a space to the end of the memo field.  This means you can get rid of the "IS NOT NULL" test.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"for second solution, i am getting this error:
invalid procedure call"

OK .. that is because there are also Zero Length Strings ("") .... which should *never* be allowed to begin with ... stand by.

mx


0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Bigger problem Dale ...

mx
0
Dale FyeCommented:
Joe,

How so?  Adding the space (" ") to the end of the memo field will resolve both the NULL and the Zero length string issue.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
try this

1)

SELECT Table1.FIELD1
FROM Table1
WHERE (((Table1.FIELD1)>"") AND ((Asc(Nz([FIELD1],0)) Between 97 And 122)=True));


2)

SELECT Table1.FIELD1
FROM Table1
WHERE (((Table1.FIELD1)>"") AND ((Asc(Nz([FIELD1],0)) Not Between 65 And 90)=True));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well this:

2)
SELECT Table1.FIELD1
FROM Table1
WHERE Asc([FIELD1] & " ") NOT Between 65 And 90

will also return Nulls, but maybe that is desired ?

1)

SELECT Table1.FIELD1
FROM Table1
WHERE Asc([FIELD1] & " ")  Between 97 And 122


0
Dale FyeCommented:
Joe,

Good point about the NOT uppercase.  I made an assumption that Not Uppercase would include NULL, but if the OP doesn't want records where the memo field is Null or blank (empty string) then we could modify the WHERE clause to:

SELECT Table1.FIELD1
FROM Table1
WHERE ([FIELD1] & " " <> " ") AND Asc([FIELD1] & " ") NOT Between 65 And 90

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Dale ... you're right. When I first tried that, I got an error also ... fluke.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
btw ... by 'Bigger problem Dale ..." ... I meant there are apparently also Zero Length Strings besides Nulls ... I hit Submit accidentally before adding that comment.

mx
0
gleveratorAuthor Commented:
Hi, used fyed's first examples:
WHERE Asc([FIELD1] & " ") Between 97 And 122

and

WHERE Asc([FIELD1] & " ") NOT Between 65 And 90

Those worked fine, had to put a NOT in front of one of them and used And is not null for one of them to get my results, but the main premise worked. thanks
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, you should reopen the Q and split the points with fyed ..

mx
0
Dale FyeCommented:
Don't worry about it.  just leave the points as they are.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.