No Duplicates, except an exception?

[DanielWalters6]

I have an indexed field, which I don't want Duplicates in... an external
supplier's ID.

However, 1 in 5 records won't have one. I know it's possible to have
multiple records with a null value for this field - Not a problem. However
I've been asked if I can store "None" in this field. - This will create a
duplicates problem as there will be around 15 records a week with "None" in
this field....

Any ideas - or do i just have to leave it blank?

 

[Armen Stein]

I have an indexed field, which I don't want Duplicates in... an external
supplier's ID.

However, 1 in 5 records won't have one. I know it's possible to have
multiple records with a null value for this field - Not a problem. However
I've been asked if I can store "None" in this field. - This will create a
duplicates problem as there will be around 15 records a week with "None" in
this field....

Any ideas - or do i just have to leave it blank?

The unique index will be enforced on any non-null values. So "None"
won't work in that field.

An alternative might be to have another field, a Yes/No checkbox, that
indicates whether there is No Supplier ID. That would distinguish
between Nulls that are unknown, and Nulls that are None.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[KARL DEWEY]

Then you can use an IIF statement like ---
IIF([IndexField] Is Null, IIF([CheckBox] = -1, "None", ""), [IndexField])
to add 'None' to your output.

 

[fredg]

I have an indexed field, which I don't want Duplicates in... an external
supplier's ID.

However, 1 in 5 records won't have one. I know it's possible to have
multiple records with a null value for this field - Not a problem. However
I've been asked if I can store "None" in this field. - This will create a
duplicates problem as there will be around 15 records a week with "None" in
this field....

Any ideas - or do i just have to leave it blank?

I'll assume the [SupplierID] field is Text datatype, as you cannot
write a text value ('None') into a Number datatype field.

How about changing the Indexed (No Duplicates) in the table to
Indexed (Duplicates OK)?

Then on the form you use for data entry, code the Form 's BeforeUpdate
event:

If Me.[ID] = "None" Then
Else
If DCount("*","TableName","[ID] = '" & Me.[ID] & "'") >0 Then
MsgBox "This ID already exists. Re-enter a different ID value."
Cancel = True
End If
End If

If 'None' is entered it will be allowed.
If a duplicated [ID] value is entered it will cancel the entry and
return focus to the form.
If the [ID] value is not a duplicate value, it will be added to the
table.

 

[Armen Stein]

How about changing the Indexed (No Duplicates) in the table to
Indexed (Duplicates OK)?

Then on the form you use for data entry, code the Form 's BeforeUpdate
event:

If Me.[ID] = "None" Then
Else
If DCount("*","TableName","[ID] = '" & Me.[ID] & "'") >0 Then
MsgBox "This ID already exists. Re-enter a different ID value."
Cancel = True
End If
End If

If 'None' is entered it will be allowed.
If a duplicated [ID] value is entered it will cancel the entry and
return focus to the form.
If the [ID] value is not a duplicate value, it will be added to the
table.

Yes, something like that would work. It isn't enforced at the
database level, so it isn't as robust, but it may suffice for this
purpose.

You would also want to avoid the duplicate check for Null also.

If Me.[ID] = "None" or IsNull(Me.ID) Then
Else
....

Armen Stein
Microsoft Access MVP
www.JStreetTech.com