Query criteria case sensitive after Access 97 to Access 2002 conversion

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?
dingmaehAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
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
 
harfangConnect With a Mentor Commented:
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
 
stevbeCommented:
<Could this be the MS-SQL compatibility mode>
no ... I just tested and that is not the case.
0
 
dingmaehAuthor Commented:
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
 
harfangCommented:
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
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.

All Courses

From novice to tech pro — start learning today.