Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

case sensitive select statement in Access 2000?

Posted on 2002-07-17
18
Medium Priority
?
313 Views
Last Modified: 2012-05-04
how can I force the Select statement to be case sensitive? I am working in CFM and I need Select * from table where X like 'Y' to be different from
Select * from table where X like 'y'

HELP!!
0
Comment
Question by:labambina
  • 6
  • 5
  • 3
  • +3
18 Comments
 
LVL 8

Expert Comment

by:dds110
ID: 7159549
Have you tried using

"Select * from table where x like '" & [ucase, lcase]('y') & "';"

Just a thought.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7159566
You can use the StrComp("Y", "y", 0) = 0 for binary comparison

Cheers, Andrew
0
 

Author Comment

by:labambina
ID: 7159637
I am also working in Access
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:labambina
ID: 7159639
I am using access 2000
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7159679
Straight from an access 2000 query,

SELECT USystblSecurityGroups.strGroupName, StrComp([strgroupname],"grpMKCITLibrary",0) AS Expr1
FROM USystblSecurityGroups
WITH OWNERACCESS OPTION;

Cheers, Andrew
0
 

Author Comment

by:labambina
ID: 7159748
thx andrew
I will check it and get back 2 u asap
0
 
LVL 2

Expert Comment

by:kiddiec
ID: 7160477
If using a form to make the comparison, look at the module behind the form in the general section it will probably have a line
'Option Compare Database'
Change this to read 'Option Compare Binary' then a will not = A
0
 
LVL 2

Expert Comment

by:kiddiec
ID: 7160520
Further to my previous answer, if working in a query you could use the following built in function to make your comparison

iif(asc(Field1)=asc(field2),"Match","Nomatch")
0
 
LVL 2

Expert Comment

by:kiddiec
ID: 7160523
If using a form to make the comparison, look at the module behind the form in the general section it will probably have a line
'Option Compare Database'
Change this to read 'Option Compare Binary' then a will not = A

0
 

Author Comment

by:labambina
ID: 7168979
none of the suggested answered worked so far....
still looking
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7170094
What is CFM? If you are using an ODBC driver to select the data then you will have to code it in CFM as JET doesn't support Case sensitive queries. Check out the MS KB article Q209674 this advises the use of ASC() or STRCOMP both of which are VBA functions.

Cheers, Andrew
0
 

Author Comment

by:labambina
ID: 7176950
cold fusion.. u know like asp :)
0
 

Author Comment

by:labambina
ID: 7176957
I use ODBC to connect
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7177743
The only thing you can do is to select the data through the ODBC driver which is not case sensitiy then process the results so you can eliminate the case sensitive stuff.

Cheers, Andrew
0
 
LVL 3

Accepted Solution

by:
YP earned 200 total points
ID: 7200518
Ciao labambina,

you can use the StrComp function for this.

From Access 97 Help
<
StrComp Function

Returns a Variant (Integer) indicating the result of a string comparison.

Syntax

StrComp(string1, string2[, compare])

The StrComp function syntax has these named arguments:

Part     Description
string1     Required. Any valid string expression.
string2     Required. Any valid string expression.
compare     Optional. Specifies the type of string comparison. The compare argument can be omitted, or it can be 0, 1 or 2. Specify 0 (default) to perform a BINARY COMPARISON. Specify 1 to perform a textual comparison. For Microsoft Access only, specify 2 to perform a comparison based on information contained in your database. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison.
Return Values

If     StrComp returns
string1 is less than string2     -1
string1 is equal to string2      0
string1 is greater than string2      1
string1 or string2 is Null     Null

>

So in your case you should use
Select * from table where StrComp([X],'Y',0) = 0 if you want only 'Y'
Select * from table where StrComp([X],'y',0) = 0 if you want only 'y'

Good luck,
YP
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7281454

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: YP
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 28

Expert Comment

by:TextReport
ID: 7281556
Nic;o, the user is doing the query in cold fusion through ODBC to jet, I don't think any of this has addressed the issue from Cold Fusion as you can't call Access function through ODBC.

Cheers, Andrew
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7281626
Good point Andrew, overlooked that !

I'll change my recommandation into:

 - PAQ and points removed

Nic;o)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question