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

[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?

[Salad]

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

[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.

[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.

[Rick Brandt]

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

[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?

[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.

[lyle fairfield]

On May 28, 9:28*am, MLH <[e-mail]:

On Tue, 27 May 2008 16:56:25 -0500, "Rick Brandt"

<[e-mail]:

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.