By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Can I force a query criteria to limit to Like "*ABC*" - forcing the cap's ?

P: n/a
MLH
If I drop Like "*ABC*" in a QBE grid criteria cell,
the records returned include mixed case. Can I
force the uppercase limitation in a QBE grid?
Jun 27 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
MLH wrote:
If I drop Like "*ABC*" in a QBE grid criteria cell,
the records returned include mixed case. Can I
force the uppercase limitation in a QBE grid?
You could create a calculated column. Ex:
UcaseField:Ucase(FieldName)
then put the filter criteria under that column. You could also use
StrConv() as well.

Hanahana
http://www.youtube.com/watch?v=VoI4-...eature=related

Jun 27 '08 #2

P: n/a
MLH
On Tue, 27 May 2008 09:53:25 -0700, Salad <oi*@vinegar.comwrote:
>MLH wrote:
>If I drop Like "*ABC*" in a QBE grid criteria cell,
the records returned include mixed case. Can I
force the uppercase limitation in a QBE grid?

You could create a calculated column. Ex:
UcaseField:Ucase(FieldName)
then put the filter criteria under that column. You could also use
StrConv() as well.

Hanahana
http://www.youtube.com/watch?v=VoI4-...eature=related
Not a bad idea. But there's one gotcha. (there's always
one of them, isn't there?)

I have rows that contain say "NC" ==the state abbreviation
for North Carolina. And I have rows that contain XYZ Company, Inc
(which has an "nc" in it). I ONLY want the rows containing the
uppercase NC anywhere in the string.
Jun 27 '08 #3

P: n/a
MLH
I've tried crit's like Like "*" & Chr$(78) & "*"

but they return strings with both N's and n's.
Forcing the uppercase characteristic of a given
letter of the alphabet may just not be possible in
a query. But I'm not giving up hope.
Jun 27 '08 #4

P: n/a
MLH wrote:
I've tried crit's like Like "*" & Chr$(78) & "*"

but they return strings with both N's and n's.
Forcing the uppercase characteristic of a given
letter of the alphabet may just not be possible in
a query. But I'm not giving up hope.
InStr() with the binary compare option is the only way to get case
sensitivity in Access.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jun 27 '08 #5

P: n/a
MLH
On Tue, 27 May 2008 16:56:25 -0500, "Rick Brandt"
<ri*********@hotmail.comwrote:
>MLH wrote:
>I've tried crit's like Like "*" & Chr$(78) & "*"

but they return strings with both N's and n's.
Forcing the uppercase characteristic of a given
letter of the alphabet may just not be possible in
a query. But I'm not giving up hope.

InStr() with the binary compare option is the only way to get case
sensitivity in Access.
Thanks Rick. OK, Option compare Binary in a procedure
until after I'm finished running the query, then set it back to
Option compare Text ==Is that the recommended way?
Jun 27 '08 #6

P: n/a
MLH
Oops...
Looking at the InStr HELP, I see. You're recommending this syntax...
MyPos = Instr(1, SearchString, SearchChar, 0)
.... the key point being the 0 value for the 4th argument. Thanks.
That is the most direct approach.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxx
On Tue, 27 May 2008 16:56:25 -0500, "Rick Brandt"
<ri*********@hotmail.comwrote:
>MLH wrote:
>I've tried crit's like Like "*" & Chr$(78) & "*"

but they return strings with both N's and n's.
Forcing the uppercase characteristic of a given
letter of the alphabet may just not be possible in
a query. But I'm not giving up hope.

InStr() with the binary compare option is the only way to get case
sensitivity in Access.
Jun 27 '08 #7

P: n/a
On May 28, 9:28*am, MLH <C...@NorthState.netwrote:
On Tue, 27 May 2008 16:56:25 -0500, "Rick Brandt"

<rickbran...@hotmail.comwrote:
MLH wrote:
I've tried crit's like * *Like "*" & Chr$(78) & "*"
but they return strings with both N's and n's.
Forcing the uppercase characteristic of a given
letter of the alphabet may just not be possible in
a query. But I'm not giving up hope.
InStr() with the binary compare option is the only way to get case
sensitivity in Access.

Thanks Rick. OK, * Option compare Binary in a procedure
until after I'm finished running the query, then set it back to
Option compare Text *==Is that the recommended way?
SELECT *
FROM Customers
WHERE StrComp([First Name],[FirstName],0) = 0

successfully selects "ANTONIO" but not "Antonio" when the parameter
[FirstName] is passed as "ANTONIO".

Changing the Option Compare Default, in my opinion, is unnecessary and
I would be hesitant about doing so.

Jun 27 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.