Limiting Records with Null Only Once

[Dan]

Hi,

How would I go about limiting a record's existence in a
table by the following criteria. Field A is not null and
field B is not null, or field A is not null and field B
is only null once.

If this can't be done in a table, can it be written into
an append query, as all of the new records are
automatically appended to the table?

Your help is greatly appreciated.

Dan

 

[Allen Browne]

You can use the Validation Rule of the table to insist that both fields
cannot be Null together, but you cannot create engine-level validation to
insist on a count of nulls in a field.

 

[Dan]

Thanks for the reply.
If I can't say the count of field B being null is less
than 1 in a table, would I be able to say it and combine
it with field A is not null in an append query.

Thanks for you help.

Dan

 

[Allen Browne]

You can limit your query such that it excludes any record where A is not
null and B is null if such a record already exists in the table. That would
involve a subquery in the WHERE clause, e.g.:
WHERE NOT EXISTS ( SELECT ...)

The problem then occurs if your append query actually adds 2 or more records
where A is not null and B is null, so you need to further limit it by
running another query on the source data of your append query to prevent 2
of those being added in the same append query.

 

[Dan]

Thank you so much for your help.

Is there a way to use the rule validation in the table to
limit duplicates of field A + field B. For example, Don't
allow Values 1 and 2 for field A and B twice, or 1 or null
twice.

Thanks again

Dan >-----Original Message-----

 

[Allen Browne]

In table design view, open the Indexes box (View menu).

You can create an index based on the two fields:
1. Enter a Name for the index, such as AB.
2. Beside the name, enter the first field: A
3. In the 2nd row, leave the Name blank, and in the 2nd column enter B.
This gives you an index named AB, on fields A and B combined.
4. In the lower pane of the dialog, set the properties to make the index
Unique.

I don't think you will be able to get that working for the Null values. JET
will either allow no nulls at all, or allow any number of nulls. One
workaround might be to use zero-length strings for B instead of nulls, if
that is appropriate.

An example where it may be appropriate: one property can have several units
such as Unit 7 or Unit 8A. We split the unit number into 2 fields (number a
suffix) so they sort correctly (i.e. you don't want to use Text and end up
with unit 11 between 1 and 2). In the case of Unit 7, there is no suffix,
but we want to insist that you cannot have two of unit 7 with nothing in the
Suffix field. So, we set these properties for the Suffix field:
Allow Zero Length Yes
Required Yes
Default Value ""
Then we create a Unique index on BuildingID + Unit + Suffix. Now we can have
only one unit 7 for the building without a suffix, because that entry uses
up the one permitted zero-length string (ZLS) for the Suffix for Unit 7.

That is a valid use of the ZLS. For our Unit 7, the suffix is not unknown
(Null) but known to be non-existent (correctly represented by a ZLS), and we
are therefore able to insist on the rule that for any building and unit
number, only one record is permitted with the non-existent suffix.

In general, the Allow Zero Length string must be set to No so the data is
not too confusing for the user, but it may be useful for the case you are
trying to handle.

 

[Dan]

I used the allow zero length string on field B with the
combined index on both fields and it worked like a gem.

Thank you so much for taking the time to help me and to
explain the solution so clearly. I really appreciate it.

Dan