<div>
I still want the ability to create a single record with a null value in the Order_Code field. &nbsp;When I try now, Access is returning &quot;You must enter a value in the tbl_Vendor_Item.Order_Code<wbr /> Field.
</div>


I still want the ability to create a single record with a null value in the Order_Code field.  When I try now, Access is returning "You must enter a value in the tbl_Vendor_Item.Order_Code Field.

<div>
Allowing null in a field that is part of a unique index is handled differently by various RDBMS. &nbsp;If this field is a string, you can set the AllowZLS property to Yes and that will allow you to store one &quot;blank&quot; value. &nbsp;If the field is numeric and is not a foreign key, you can use some arbitrary high or low value but your queries will have to always interpret that value. &nbsp;I would need to understand more about the purpose of the field and why null is a valid value to offer more concrete suggestions.
</div>


Allowing null in a field that is part of a unique index is handled differently by various RDBMS.  If this field is a string, you can set the AllowZLS property to Yes and that will allow you to store one "blank" value.  If the field is numeric and is not a foreign key, you can use some arbitrary high or low value but your queries will have to always interpret that value.  I would need to understand more about the purpose of the field and why null is a valid value to offer more concrete suggestions.

<div>
<div class="content wysiwyg-content">
I have a 3 field table index:<br />
<br />
PLU<br />
Vendor_Name<br />
Order_Code<br />
<br />
Index Properties:<br />
<br />
Primary - No<br />
Unique - Yes<br />
Ignore Nulls - No<br />
<br />
I am still able to create duplicate records in the table when Order_Code field is unpopulated/blank. &nbsp;If I enter a duplicate order_code value (lets say &quot;01&quot;), the index works, and will not allow me to create the record.<br />
<br />
Why isn't the index working when I leave the field blank?
</div>
</div>


I have a 3 field table index:

PLU
Vendor_Name
Order_Code

Index Properties:

Primary - No
Unique - Yes
Ignore Nulls - No

I am still able to create duplicate records in the table when Order_Code field is unpopulated/blank.  If I enter a duplicate order_code value (lets say "01"), the index works, and will not allow me to create the record.

Why isn't the index working when I leave the field blank?

Access table Index ignoring Nulls when set to No

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.