Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    277

    Unanswered: Can't use Nz in validation rule?!

    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:
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe it doesn't work on date/time columns??

    try IIf(IsNull([datecol]),#12/31/9999#,[datecol])
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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)
    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().
    Have a nice day!

  4. #4
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by r937 View Post
    try IIf(IsNull([datecol]),#12/31/9999#,[datecol])
    Yup, that worked. Many thanks.

    Quote Originally Posted by Sinndho View Post
    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.
    Last edited by futurity; 01-17-12 at 19:22.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by futurity View Post

    ...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)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •