MS Access: Multi-Field Unique Index Not Working

Question

My unique index over 3 fields is not working. I was able to enter a duplicate. All of my fields are set to "Required" and do not have any Nulls. I have already tried re-creating the table and index. In a query of my table, one of my duplicates does not show up when it should.

My query was:

SELECT Transactions.Transaction_ID, Transactions.Transaction_Time
FROM Transactions
WHERE (((Transactions.Transaction_Time)=#9:17:46 PM#));

However, this query retrieves both duplicates:

SELECT Transactions.Transaction_ID, Transactions.Transaction_Time
FROM Transactions
WHERE (((Transactions.Transaction_Date)=#2/3/2018#));

As does this one:

SELECT Transactions.Transaction_ID, Transactions.Transaction_Time
FROM Transactions
WHERE (((Transactions.Amount)=-68.47));

What's going wrong here?

Answer 1

Creating an unique index on a date or time value is a risky thing, because of two reasons:

1: A time field is always a DateTime field. The date is just not displayed. But the date certainly can be inequal. between those two duplicates. The same goes for date fields: they're DateTime too, with the time not being displayed.

2: DateTime fields in Access are actually floating numbers. This means that while only the time in seconds may be displayed, there can be a difference of a fraction of a millisecond between two values. See the discussion and answer on this question.

Likely, one or both of these reasons is causing the unique constraint not to trip.