Can't use Nz in validation rule?!

futurity · #1 (**permalink**) 01-17-12, 12:25

Using Access 2007, trying to create a table/row-level validation rule on an existing table that already has data. Here's my rule:

Code:

Nz([start_date], #12/31/9999#) <= Nz([end_date], #12/31/9999#)

Here's the error I get when Access tries to validate existing data against the new rule:

Quote:

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

So ... is Access really telling me it doesn't recognize one of it's own built-in functions, even though the function is included in the list of available built-in functions in the expression builder window?

Please, someone tell me I'm doing something stupid or missing something obvious here.

r937 · #2 (**permalink**) 01-17-12, 12:47

maybe it doesn't work on date/time columns??

try IIf(IsNull([datecol]),#12/31/9999#,[datecol])

Sinndho · #3 (**permalink**) 01-17-12, 12:54

I'm not sure that Nz() is accessible from the Jet engine. Nz() (and also EVal() and the domain functions: DLookUp(), DCount(), etc.) is a member of the Microsoft Access xx.x Object Library (Access.Application) , not of the VBA library.

From Allen Browne (Microsoft Access tips: Validation Rules)

Quote:

You cannot use a validation rule where you want to call user-defined functions, or VBA functions beyond the ones in JET such as IIf() and Date().

futurity · #4 (**permalink**) 01-17-12, 18:13

Quote:

Originally Posted by r937

try IIf(IsNull([datecol]),#12/31/9999#,[datecol])

Yup, that worked. Many thanks.

Quote:

Originally Posted by Sinndho

I'm not sure that Nz() is accessible from the Jet engine.

Well, except you can use Nz() in queries:

Code:

select *
from mytable
where nz(start_date, #12/31/9999#) > nz(end_date, #12/31/9999#)

Not that you're wrong, but it seems pretty arbitrary that a built-in function that works with plain SQL queries is suddenly not recognized when trying to implement table constraints.

Another fruit basket to Micros**t for wasting my time with their broken software. They must be piling up on their desks by now....

P.S., thanks for the link; very helpful.

Missinglinq · #5 (**permalink**) 01-17-12, 20:57

Quote:

Originally Posted by futurity

...it seems pretty arbitrary that a built-in function that works with plain SQL queries is suddenly not recognized when trying to implement table constraints.

Not really, when you think about it! The entire point of Queries is facilitate the manipulation of data, so having data-manipulating Functions work within them makes perfect sense. The entire point of Tables is to store data...period! Tables are not meant to be used for data manipulation.

Linq ;0)>

Internet Services

Web Development

Digital Marketing

Consumer Tech