Hello,

It seems that if I include in a unique index for a table the four fields:

PerTypeID (required/not null)
PerFname (req/not null)
PerMI
PerLname (req/not null)

...that I was able to put in duplicates records if the MI was left null. How can I get past this problem?

Thoughts on adding MI to be required and auto set to blank if no value is entered? What is the cleanest way to do this? Does this make sense?

Am I thinking about this wrong?

I need coffee... :)

Set a multi-field index (http://www.btabdevelopment.com/main/QuickTutorials/A2K7Howtocreateamultifieldindex/tabid/140/Default.aspx).

...sorry. Do need coffee... This assumes (as I expect to find) that I don't always have a MI for the person. But can reasonably assume (since there may be many people in this table) that I will find multiple "John (null) Smiths"

Thoughts? Thanks :)

Oh, didn't see your reply. In fact, I think that is what I have.

I have a single index that has the four parts mentioned above (set to index/no duplicates), but that I was able to insert duplicates when I left the MI field null.

It is like the nulled MI field wasn't comparable to find that it was breaking the unique index rule...

Oh, didn't see your reply. In fact, I think that is what I have.

I have a single index that has the four parts mentioned above (set to index/no duplicates), but that I was able to insert duplicates when I left the MI field null.

It is like the nulled MI field wasn't comparable to find that it was breaking the unique index rule...

You can't include PerTypeID in the multi-field index because that will always make the line unique so you can put in nulls.

I am not showing my problem well. :)

I attached three pics.

In the first, it shows the current stucture.

The second shows how the Unique index is thwarted because the MI is left null...

The third "proves it" by my adding a value (making it not null) and able to bee seen that the unique idx is working - just being thwarted when a composite unique key contains a null value...

Does this make sense?

It makes sense, but it would be - again as I said, better if you removed PerFieldID from UnqIdx.

Hey Bob,

Not to sound rude (honestly), but that doesn't have any bearing on the problem of a unique index "not working as expected/properly" because a parent fkey field is/is not available as part of the composite unique index.

I attached pics showing that I can write duplicates, which then are thwarted when I add a non-null value to the MI column (which is the only not required column).

Maybe I should be thinking about storing the ID field's value in there when null? Or something else to make it comparable for unique checking?

Thoughts?

Guess you'll really need to do the validation at form level. Even though it SHOULD work, it doesn't.

Yea, I would have thought that the single instance of a "matt" "(null)" "gorman" would have been acceptable in MS Access as a single allowable instance... with additional instances being tossed out. But I guess not.

I never noticed this before... Null are not comparable to each other to see if they break a unique idx. Rats...

I'll post back when I think of something. :)

Thanks!

Hmmm... I guess I am either stuck with adding another comparable field (maybe the person's zip code from their primary address) or doing something odd/fancy like, using the perID in that blank field when/where null...

...or training the user to press the space bar when no value is there, as per the instructions on the "allow zero length string = Y" as per the image.

Setting the allow zero length=Y, and pressing a spacebar seems to make the field not null, which is sufficient to have it able to be a duplicate if another "matt" "[zero len]" "gorman" is entered...

Not what I hoped for. But it is a way...

Well, if your user is using a FORM for input (which they should be doing as you do not have control over things at table level), then they should not need to do so. You can validate from the form.

i had the same problem you have, and it really is a pain.

i was trying to leave a date field blank to indicate a special price that didnt expire at any particular date, but tried to include the date in an index to ensure there could be only 1 active price record - however, I had exactly the same issue you describe - access allowed multiple matching keys on a OTHERWISE unique index, when one the key fields was null

this was actually reported and logged to MS - not sure if it ended up as a KB article though

the only solution is not to have nulls. I ended up having to have a date of 0 to get round the index issue, but then i had to test the date for a value of 0, as a date of 0 is now smaller than other dates, which made the code quite a bit more complex

ideally, i would have set date = maxlong .... except there is no maxlong constant available in vba directly