How can I make Access 2007 not case sensitive?

[luckybug]

Hi there,

I am new here and I hope I won't break any rules already with posting this one.

My Access 2007 is case sensitive and I would like to know how I can change this, I have a lot of data that are the same but sometimes capitalized and sometime not and it would make my life so much easier if it wouldn't be case sensitive anymore.

Thank you so much for your help in advance!!
Luckybug

 

[CJ_London]

Access is not case sensitive so CASE=case which can be a problem when looking at data from other systems which are case sensitive.

However there are things you can do. For example you can set the format of controls to display in upper or lower case by using > or <. Note this does not change the underlying format of the date.

You also have functions you can use is sql or vba to convert field values into the format you want- perhaps on importing data for example

these are Upper, Lower and StrConv (equivalent of Excel's Proper)

Perhaps a previous developer has been using these in their design?

Why do you think it is case sensitive? - can you demonstrate a situation where this happens

 

[Galaxiom]

Access isn't case sensitive.

VBA defaults to not case sensitive via the declaration: Option Compare Database

A module can be made case sensitive with the declaration: Option Compare Binary

 

[CJ_London]

A module can be made case sensitive with the declaration: Option Compare Binary

I didn't know that, tho' I'm not sure what the benefit is - does Find become case sensitive in that module?

 

[luckybug]

First of all - thank you so much for the fast reply!!

Ok let me give you an example, if I for example say :
Select * from test where testcolumn like "%test%"
I get only data back that include "test" but I wouldn't get any records back that include "Test" or "TEST". What can I do that I get all 3 records back?

Hope this makes sense and thanks again for the help!

 

[Galaxiom]

Wildcard character in Access is the asterisk *.

 

[CJ_London]

Have you ticked the ANSI 92 (SQL Server Compatible Syntax) in File/Options/Object Designers?

if yes then it shold be Alike "%test%"

If no, it should be like "*test*"

 

[luckybug]

Sorry yes of course it is "*test*". Was accidentally using MySQL syntax.

 

[Galaxiom]

I didn't know that, tho' I'm not sure what the benefit is - does Find become case sensitive in that module?

I don't know about the benefits but I bet it would leave another developer scratching their head if they didn't notice.

It only affects comparisons in VBA. So testing two strings for being equal becomes case sensitive. InStr() defaults to case sensitive.

Methods like Find and Filter are not affected, presumably because they are performed on the recordset by the database engine.

 

[luckybug]

I probably should make clear that the syntax was only wrong here in my issue description.it is correct in my real query,so I still have the problem!
I have this issue also using the filter option "contain"

 

[gemma-the-husky]

what happens if you search for Test, or TEST

maybe the data is not stored exactly as it seems to be.

I must say I have never had an issue with case sensitivity. I struggle to understand how they do it, though.

eg how they manage to treat "a" and "A" as equivalent, when they have different ASCII codes.

 

[luckybug]

If I search for "*Test*" then I won't get test or TEST, and the same with "TEST" I wil only get records that include TEST.
My co-worker does not have the issue so I thought that must be a setting issue.

 

[Pat Hartman]

If your tables are Jet/ACE then those rules apply and Jet/ACE are NOT case sensitive.
If your tables are MySQL then the MySQL rules apply. I don't use MySQL so I don't know how to change case sensitivity. I would expect it to be at the database level since that's where the queries are processed so it doesn't make sense that you and your neighbor have different results. Perhaps it is a setting in the ODBC driver. I would search a MySQL site for clarification.

 

[luckybug]

I am not using MySQL I am using Access 2007

 

[Pat Hartman]

Access 2007 is a rapid application development environment. What database engine are you using? If it is the default "Access" database engine, then for A2007, you are using ACE and ACE is NOT case sensitive.

 

[Galaxiom]

I must say I have never had an issue with case sensitivity. I struggle to understand how they do it, though.

eg how they manage to treat "a" and "A" as equivalent, when they have different ASCII codes.

When working in a case sensitive environment and requiring the case to be ignored one typically applies UCase() to everything before comparison.

This would also be a workaround to luckybug's odd problem. However it would be rather inefficient having to apply UCase to every record on searches.

 

[Galaxiom]

Something is very odd about this problem. Does it behave this way when run on other computers?