How do I make data entry case sensitive?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm in datasheet view. I enter data for record one, Abc. I enter data for
record two, ABc. These two records should be unique. Access says I have a
duplicate record. How do I make Access recognize them as unique?
 
You can't.

At the engine-level, Access is case-insensitive.
So if you have a unique index on the field, you will not be able to enter 2
records for Abc and ABc.

If that is essential, you would need to use a different back end than JET
(i.e. attached tables, not tables in Access.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
Allen said:
You can't.

At the engine-level, Access is case-insensitive.
So if you have a unique index on the field, you will not be able to enter 2
records for Abc and ABc.

If that is essential, you would need to use a different back end than JET
Click to expand...

....or the OP could use a Jet 4.0 CHECK constraint that parsed out the
characters, used each character's ascii code to construct a key for
each row and test there are no duplicates.

For a similar example, here's one I did earlier to only allow lowercase
letters:

http://groups.google.com/group/microsoft.public.access/msg/5738df67139469bf

Jamie.

--
 
Jamie said:
...or the OP could use a Jet 4.0 CHECK constraint that parsed out the
characters, used each character's ascii code to construct a key for
each row and test there are no duplicates.
Click to expand...

Assuming the column is three ascii characters, the following Jet 4.0
CHECK constraint will have the effect of a case sensitive unique
constraint:

CREATE TABLE Test3 (
data_col CHAR(3) NOT NULL)
;

ALTER TABLE Test3 ADD
CONSTRAINT test3__case_sensitive_unique
CHECK (
NOT EXISTS
(
SELECT
FORMAT$(ASC(MID$(T1.data_col, 1, 1)), '0000')
& FORMAT$(ASC(MID$(T1.data_col, 2, 1)), '0000')
& FORMAT$(ASC(MID$(T1.data_col, 3, 1)), '0000'),
COUNT(*)
FROM Test3 AS T1
GROUP BY
FORMAT$(ASC(MID$(T1.data_col, 1, 1)), '0000')
& FORMAT$(ASC(MID$(T1.data_col, 2, 1)), '0000')
& FORMAT$(ASC(MID$(T1.data_col, 3, 1)), '0000')
HAVING COUNT(*) > 1
)
);

INSERT INTO Test3 (data_col) VALUES ('Abc')
;
INSERT INTO Test3 (data_col) VALUES ('ABc')
;
INSERT INTO Test3 (data_col) VALUES ('xYz')
;
INSERT INTO Test3 (data_col) VALUES ('xYz')
;

The CHECK only bites for the last insert i.e. does a case sensitive
comparison.

Jamie.

--
 
If you define a text field as binary instead of text,
you can put a binary unique index on it, instead of
a text index.

It is one of the options discussed (with some of the
limitations and drawbacks) at
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244693
How To Perform a Case-Sensitive JOIN Through Microsoft Jet

The biggest drawback is that you probably can't enter
data directly into your datasheet view (unless you use Access 2.0)

(david)
 
Back
Top