Solved

Query criteria case sensitive after Access 97 to Access 2002 conversion

Posted on 2007-04-05
5
1,606 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
5 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 63 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 62 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now