Results 1 to 6 of 6

Null Date value display in Microsoft Access 2007

  1. #1
    Owl
    Owl is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Location
    Durban, South Africa
    Posts
    55

    Null Date value display in Microsoft Access 2007

    #Error in Empty Date field

    In a database for Disciplinary Hearings, where the ValidityEndDate is calculated depending on the entry in WarningType and thus NoMonths and the entry in ValidityStartDate.

    If there is a date entered in ValidityStartDate it works perfectly.

    When there is no entry in the field, ValidityStartDate, this causes #Error to be displayed in the field ValidityEndDate. I donít want that #Error to be displayed. I understand that the problem exists because of multiplying by zero, but I need to know how to resolve it e.g. with Conditional Formatting if all else fails.

    My criteria for the calculated query field (ValidityEndDate) are as follows:

    CDate(DateAdd("m",([NoMonths]),([ValidityStartDate])))

    Thank you in anticipation.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Ski Town USA
    Posts
    2,000
    Not sure I get the whole issue, but it sounds like you may need to use the NZ function in your calculation. Look here for information on that.

    http://www.techonthenet.com/access/f...dvanced/nz.php

    Alan
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  3. #3
    Owl
    Owl is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Location
    Durban, South Africa
    Posts
    55
    Thank you Alan. However, I had tried every variable of the Nz function that I could think of - and when you posted your reply, I tried the ones suggested in the link, but none of them made any difference.

    I may be wrong, but it seems to me that you can't use Nz in a Date field.

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Ski Town USA
    Posts
    2,000
    Owl;
    Take a look at this link and see if this helps. Just another way of looking at nulls in date field.

    http://forums.aspfree.com/microsoft-...on-219928.html

    Alan
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  5. #5
    Owl
    Owl is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Location
    Durban, South Africa
    Posts
    55
    Oh Alan, you STAR!

    In that link, I saw that there was no CDate and when I took mine out, it worked like a dream!

    Thank you!

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Ski Town USA
    Posts
    2,000
    You are welcome.
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-02-2011, 05:49 AM
  2. Replies: 2
    Last Post: 07-29-2011, 10:43 AM
  3. Replies: 3
    Last Post: 06-22-2010, 06:49 AM
  4. Developer Option in Microsoft Access 2007.?
    By aligahk06 in forum Forms
    Replies: 1
    Last Post: 04-23-2010, 12:44 PM
  5. microsoft access 2007 video tutorials
    By jamesdean in forum Access
    Replies: 0
    Last Post: 06-08-2007, 07:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums