Results 1 to 4 of 4
  1. #1
    mastee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    2

    Creating Formula In The Form

    Dear All,
    Need help in creating formula in the forms . I am trying to subtract (expiring date - start date = Duration) . Can some please help me out in this.
    Thanks in advance.

    Regards,

    Mastee





  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    =IIf(IsNull([expiring date]-[start date]),0,([expiring date]-[start date]))

    you can stick with just =[expiring date]-[start date] if you want but the reason for the iif statement is to not leave you with blanks in case of null values. In access, any calculation with a Null returns Null. the iif statement will catch that. Feel free to replace the 0 with whatever you want (dont forget " " for strings).

    If that's all you need, please mark thread solved.

  3. #3
    mastee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    2
    Thanks Shabz . I tried but its giving me error.

  4. #4
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Code:
    =Iif(IsNull([Start Date]),0,Iif(IsNull([End Date]),0,DateDiff("dd",[Start Date],[End Date])))
    The above should return the number of days between [Start Date] and [End Date]. If either of the dates isn't entered, then it will display 0.

    If you want the difference in months, just change "dd" to "mm". Or "yyyy" for years.

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

Similar Threads

  1. Creating Filters on a Form
    By Nixx1401 in forum Access
    Replies: 4
    Last Post: 02-27-2010, 05:32 AM
  2. Creating an Inventory Form in Access
    By KIDRoach in forum Forms
    Replies: 0
    Last Post: 09-13-2009, 11:39 PM
  3. Creating Form from Normalized Tables
    By heathers in forum Forms
    Replies: 2
    Last Post: 09-10-2009, 03:43 AM
  4. Replies: 14
    Last Post: 06-24-2009, 07:36 PM
  5. form formula to combine two fields
    By InvGrp in forum Forms
    Replies: 1
    Last Post: 10-20-2006, 12:10 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