Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Calculating on a Null Date Field

    I administer an Access database to track employee training for my company. I append a training record to the Training History table at the time the employee is notified (NotifiedDate) of training and I complete the record when I receive (DateReceived) the evidence of training. I run reports to determine who is Compliant and who is Delinquent. The query calculates the DateDue and I use an IIF function to determine Compliant vs Delinquent - IIF([DateReceived]>[DateDue], "Delinquent", "Compliant"). However, when the DateReceived remains Null the results of the query return Compliant for that record. If the current date is greater than the DateDue my employee is Delinquent! Can you help me fix my query so it takes the Null date into account? Thank you.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    If you want to change the Null Date Field value to Current Date then change the expression as follows:

    Code:
    IIF(Nz([DateReceived],Date())>[DateDue], "Delinquent", "Compliant")
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Oct 2011
    Posts
    2

    Calculating on a Null Date Field

    It absolutely worked! Thank you so very much for your good help!

Posting Permissions

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