Results 1 to 5 of 5
  1. #1
    Marlene23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    14

    Date Difference calculation

    Good Day

    I am hoping that there is an Access expert that can please help me with a formula.

    I have create a query and managed to work out the number of days difference between 2 dates. I would however like to take it one step further ...

    I have a field called "VAFStatus"

    What I want to do is to work out the number of days between TODAYS date and the date the record was captured (VAFDate)

    BUT ...

    When the option "Filled" from the VAFStatus dropdown field is selected the number of days should be worked out between "VAFDate" & VAFDateFilled" (2 date fields)

    My formula I would use in excel looks like this:



    =IF(C1="Filled",DAYS(B1,A1),DAYS(NOW(),A1))

    Thank you in advanced!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    date math is done with DATEDIFF, and DATEADD.

    you want:
    =DATEDIFF("d",VAFSTatus, Date())

  3. #3
    Marlene23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    14
    This was my attempt at building an expression but I get an ERROR when running the query:

    IIf([tbl1VAFLog]![VAFStatus]="Filled",DateDiff("d",[tbl1VAFLog]![VAFDate],[tbl1VAFLog]![DateFilled]),DateDiff("d",[tbl1VAFLog]![VAFDate],Now()))

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need an IIF,
    put both calcs in the query regardless.
    then on the form , show it if FILLED , make 1 visible

    if VAFStatus = "filled" then
    txtDate1.visible = true
    else
    txtDate2.visible = true
    endif

  5. #5
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by Marlene23 View Post
    This was my attempt at building an expression but I get an ERROR when running the query:

    IIf([tbl1VAFLog]![VAFStatus]="Filled",DateDiff("d",[tbl1VAFLog]![VAFDate],[tbl1VAFLog]![DateFilled]),DateDiff("d",[tbl1VAFLog]![VAFDate],Now()))

    IIf() evaluates both the true and false parts whether or not the condition is true or false. So if either the true or false expression returns a run-time error, IIf() will return a run-time error. My guess is when your date field is not filled, it has a non-date value, which triggers a run-time error in DateDiff().

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

Similar Threads

  1. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  2. Replies: 1
    Last Post: 10-08-2015, 02:20 PM
  3. time difference calculation help
    By JayRab in forum Access
    Replies: 5
    Last Post: 02-03-2014, 01:32 PM
  4. Replies: 2
    Last Post: 04-10-2013, 01:37 PM
  5. Replies: 1
    Last Post: 02-12-2013, 03:48 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