Results 1 to 9 of 9
  1. #1
    Kusaf8 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    4

    Using current computer date in iif statement

    Hello all, I've exhausted my efforts and was unable to find my answer anywhere so far. I'm trying to get one of my database table columns to auto populate based on a calculated iif statement.

    Iif ([test date] < Date() - 365, "Overdue", "Complete")

    Date() is the current date I think.
    So if the data in the test date column that's in the system is over a year old, I want this column to say Overdue. Otherwise say complete.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You do date math with DateAdd,or DateDiff.

    1 year ago = DateAdd("yyyy",-1,date())

  3. #3
    Kusaf8 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    4
    Thanks Ranman. I'll have to give that a try tomorrow from work. I'll let you know if it works.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would try:
    Code:
    x = IIf([test date] < (Date - 365), "Overdue", "Complete")
    Note the parenthesis....

  5. #5
    Kusaf8 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    4
    Both code's had errors and wouldn't compile. Sometimes I don't explain things as well as I think I do. So column A would have a manually inputted date that an employee completed training [test date]. This training must be completed on an annual basis. Once that date is older than a year, I would like the column to change from complete to overdue.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    This (tested) does the job:

    =IIf([test date]<DateAdd("yyyy",-1,Date()),"Overdue","Complete")

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Kusaf8 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    4
    Do I need to change the "yyyy" if the date format in the [test date] block is dd mmm yyyy? Also would I need to change the -1? The date is pulled and updated from a web based program.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Don't understand your question.

    Suggest google 'DateAdd' to find out what it does and how it works and also google 'datetype' so you can find out how a date is stored and how it is formatted

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    No...use the exact code I gave! As I said, it has been tested and works, assuming that [test date] is defined as a DateTime Field, as it has to be, in Access, to do this kind of thing.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 3
    Last Post: 02-16-2016, 05:02 PM
  2. Adding a Count function to current select statement
    By johnson8809 in forum Queries
    Replies: 2
    Last Post: 02-21-2015, 07:32 PM
  3. Replies: 21
    Last Post: 06-27-2014, 07:33 AM
  4. Replies: 4
    Last Post: 11-29-2012, 12:45 AM
  5. Replies: 1
    Last Post: 05-25-2012, 10:36 AM

Posting Permissions

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