Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

case sensitive select statement in Access 2000?

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
labambina
Asked:
labambina
  • 6
  • 5
  • 3
  • +3
1 Solution
 
dds110Commented:
Have you tried using

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

Just a thought.
0
 
TextReportCommented:
You can use the StrComp("Y", "y", 0) = 0 for binary comparison

Cheers, Andrew
0
 
labambinaAuthor Commented:
I am also working in Access
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
labambinaAuthor Commented:
I am using access 2000
0
 
TextReportCommented:
Straight from an access 2000 query,

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

Cheers, Andrew
0
 
labambinaAuthor Commented:
thx andrew
I will check it and get back 2 u asap
0
 
kiddiecCommented:
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
 
kiddiecCommented:
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
 
kiddiecCommented:
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
 
labambinaAuthor Commented:
none of the suggested answered worked so far....
still looking
0
 
TextReportCommented:
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
 
labambinaAuthor Commented:
cold fusion.. u know like asp :)
0
 
labambinaAuthor Commented:
I use ODBC to connect
0
 
TextReportCommented:
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
 
YPCommented:
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
 
nico5038Commented:

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
 
TextReportCommented:
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
 
nico5038Commented:
Good point Andrew, overlooked that !

I'll change my recommandation into:

 - PAQ and points removed

Nic;o)
0
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 6
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now