indexing - Not allowing multiple Null values in an Access 2010 multi-column Index

Question

I'm trying to create a table in Access 2010 which will not allow duplicates in two fields, but will allow nulls in one of those fields providing there is only a single null value (so no duplication of value/null).

My table fields are as below with the ID field set as a Primary Key and the plan is to not allow duplicates in CostCode/TeamID but TeamID can be Null once for each instance of a CostCode.

The picture below shows that I can't add a CostCode and TeamID twice if they both have values, but I can add a CostCode twice with Null values in TeamID.

Is there anyway to achieve this?
I've read I could give TeamID a default value of an empty string (or 0 as that will never be a TeamID) but I'd like to use Null if possible as that is what the empty string or 0 would represent.

EDIT:
After the comment from JJ32 and a weekend to think it through I've gone with putting the TeamID value into a separate table.
I would then have a Many-2-Many join between tbl_BranchDetail and tbl_CostCodes and a Many-2-Many join between tbl_CostCodeM2MJoin and tbl_Teams.

This will remove Null values from occurring in either Many-2-Many table and my query will now read as:

SELECT       M2M.BranchID
            ,M2M.CostCodeID
            ,TM2M.TeamID
            ,CC.CostCode
            ,TM.TeamName
FROM        ((tbl_CostCodes CC INNER JOIN tbl_CostCodeM2MJoin M2M ON CC.ID = M2M.CostCodeID)
                               LEFT JOIN tbl_CostCodeToTeamM2MJoin TM2M ON (M2M.BranchID = TM2M.BranchID AND
                                                                           M2M.CostCodeID = TM2M.CostCodeID))
                               LEFT JOIN tbl_Teams TM ON TM2M.TeamID = TM.ID     

Answer 1

I don't believe it is possible to disallow duplicate nulls in a unique composite index since no two Nulls are ever considered equal.

So in your example above you'd have three unique rows, one with a combination of TBC/1 and two with a combination of TBC/null.

The only answer I know of, unfortunately, is to choose some non-null value to represent null in TeamID, and then display the result as empty within the application.