ms-accessvalidationrules

Table validation rule error: Unknown function 'Nz' in validation expression or default value on 'TableName'

In Microsoft Access, I am trying to enforce table validation to have one and only one foreign key filled using table validation rule on Long Integer ...ID fields:

Nz([MeasurementPointID], 0) = 0 Xor Nz([MeterID], 0) = 0

But when saving table definition (table name is Readout), the following error is displayed:

Unknown function 'Nz' in validation expression or default value on 'Readout'

and this is correct, because on pressing the … button, function Nz() is missing from Expression Builder.

How to implement the above requirement without Nz()?

Solution

  • A more general rewrite is just use IIF, which is available, and is a dropin replacement for Nz

    Nz([MeasurementPointID], 0) becomes IIF([MeasurementPointID] IS NOT NULL, [MeasurementPointID], 0)

    Generally, casting back and forth to a string will take a large performance hit.