Solved

Query criteria case sensitive after Access 97 to Access 2002 conversion

Posted on 2007-04-05
5
1,612 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

838 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