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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.