Results 1 to 6 of 6
  1. #1
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141

    Getting the difference between dates

    I have created a form wherein I set up the following dates:



    Text119 (current date) formula used is =Date()
    Submit date box - set up as date entered by the user
    Deadline date - set us as date entered by the user

    If the submit date box is empty, the difference will be [deadline date]-[text119]
    if the submit date box is not empty, the difference will be 0, the formula set up for the difference is as shown below:

    =IIf(IsNull([submit]),[DeadlineDate]-[text119],0)

    the result showing is an error #NAME?

    however, earlier it was showing the correct difference.

    can anybody assist me on this.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Don't overlook Google for researching

    See Datediff



  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    I agree with orange vis-à-vis using DateDiff() for this kind of thing, but that's not causing your problem, here. When #NAME is returned in a Control, especially in a Calculated Control, like this, it means that Access cannot find one or more Controls that are part of the Calculation. You could have a Control's name misspelled,or it's simply not available to the Form. So, first off, check the spelling of all Control names.

    It's odd that you have descriptive names for submit and DeadlineDate, but the Access-assigned name of text119 for the third Control. Have you changed one of the Control names since the Form last worked correctly? If so, double-check that these changes 'took.' In later editions of Access, sometimes if you change a property, such as Name, and exit the Property Pane or save the file without first moving to another property, the change doesn't actually occur.

    Is it possible that when submit is empty, either DeadlineDate or text119 could also be empty?

    Linq ;0)>

  4. #4
    trident is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    141
    Dear Missinglinq and Orange,

    thank you for taking time.

    I have made a duplicate of the form before the additional changes I made, I went back to the old form and compared the property sheet of both forms. The property sheet, table data are all the same.

    I tried creating the same formula, copy the formula from the duplicate form and the same error occurred. #name?

    I really am stump on this. Hope additional explanation can be given.

    Trident.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  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,018
    Quote Originally Posted by trident View Post

    ...Hope additional explanation can be given...Trident.
    For us to help anymore, I think you need to follow June7's suggestion and post the file.

    Linq ;0)>

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

Similar Threads

  1. Find difference of values between dates
    By Miquel1 in forum Queries
    Replies: 8
    Last Post: 12-10-2013, 10:37 AM
  2. Difference between Dates/Times
    By dr4ke in forum Queries
    Replies: 3
    Last Post: 06-26-2012, 06:30 AM
  3. Finding the difference between the dates of two records
    By jamesborne in forum Programming
    Replies: 5
    Last Post: 01-19-2012, 06:48 PM
  4. Replies: 10
    Last Post: 12-27-2011, 01:20 PM
  5. Count difference between two dates
    By Costa in forum Forms
    Replies: 3
    Last Post: 03-09-2010, 10:38 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