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

    Count Number of Days

    Hi There!



    So, the MS Access bug bit me big time and I am currently building a HR Recruitment Database for my Company. I am however not clever enough when it comes to building complicated formulas and are hoping that there will be someone to assist me.

    I have:
    A field called “DateonVAF” (This is the date when the vacancy was requested)
    A field called “VAFStatus” (This indicated wether the vacancy is open, interviewing, filled e.g.)

    What I want to do in the “NumberofDaysOpen” field is to calculate the days the VAF has been open between the current date and the “DateonVAF”
    BUT here is my biggest snag …
    I want to stop counting the number of days open once the status in the “VAFStatus” field has change to “Filled” and then that last value (final number of days) must stay unchanged in the “NumberofDaysOpen” field.

    Hoping someone can assist 😊
    Good Day
    MS Access Newbie

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Most developers *only* put calculations in queries and not fields in tables. Have you seen this link yet? http://allenbrowne.com/casu-14.html

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Marlene23,

    I would add a StatusDate field which would be helpful in itself (to know when the vacancy was requested or filled) and use that in the calculation:
    NumberOfDaysOpen=iif([VAFStatus]="Filled",[StatusDate]-[DateonVaf],Date()-[DateOnVAF])

    Cheers,
    Vlad

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

Similar Threads

  1. Count days of month with an activity
    By paultje_bos in forum Queries
    Replies: 2
    Last Post: 11-08-2016, 09:21 AM
  2. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  3. Count Days...
    By sdc1234 in forum Queries
    Replies: 1
    Last Post: 02-06-2014, 09:31 AM
  4. Count how many days since and on what date
    By burrina in forum Queries
    Replies: 28
    Last Post: 01-26-2013, 11:57 AM
  5. Count number of days
    By ramindya in forum Queries
    Replies: 1
    Last Post: 02-22-2012, 04:47 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
  •  
Other Forums: Microsoft Office Forums