?
Solved

Query criteria case sensitive after Access 97 to Access 2002 conversion

Posted on 2007-04-05
5
Medium Priority
?
1,619 Views
Last Modified: 2011-10-03
I just converted an Access 97 database to Access 2002.  Now some of the query where criteria is not working the same way.

The problem is that Access 97 query critieria seemed to be case insensitive, but the Access 2002 query now seems to be case sensitive.

For instance, "eternal" would select "Eternal" in Access 97.  The Access 2002 version does not return any "Eternal" records because the criteria is "eternal".  If I change the criteria to "Eternal", I get the "Eternal" records.  Can I change the behavior globally to be case insensitive, as before?
0
Comment
Question by:dingmaeh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 189 total points
ID: 18856165
I didnt think MSAccess was case sensitive. Perhaps a compact/repair to help rebuild, see if that makes a difference.
Is this just in a query right? This query isnt using anything like StrComp or something to help make it into a case sensitive search?
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 186 total points
ID: 18856310
Could this be the MS-SQL compatibility mode? In normal Jet mode, criteria is case insensitive. Please tell us which of these work:

    Like "[Ee]ternal"
    Like "*ternal"
    Like "%ternal"
    Like "?ternal"
    Like "_ternal"

(°v°)
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18857019
<Could this be the MS-SQL compatibility mode>
no ... I just tested and that is not the case.
0
 

Author Comment

by:dingmaeh
ID: 18857040
Thanks guys.  I thought that Access was not case sensitive, but the confirmation is important when trying to troubleshoot.  If I were going to go further, the Like tests may have been significant.

However, the problem is more goofy than just a conversion issue.  The short answer is that what were once text keys in a table are now a binary data type.  When I change them back to text, the query over this table behaves and is case insensitive.

The conversion to Access 2002 from Access 97 had nothing to do with this.  When I converted, the text keys stayed text keys.  The key data type gets changed as part of a Make Table query that seems to work differently in Access 2002.

I am splitting the points for a fast response and helping me pinpoint my real issue.
0
 
LVL 58

Expert Comment

by:harfang
ID: 18859019
Ah, yes. Binary fields would do that to you (and I have used them on occasion for that very reason).
I'm glad you found the solution to your problem!
(°v°)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

752 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