Results 1 to 8 of 8
  1. #1
    sljedwards is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    5

    Question Random "#NAME?" error being returned from DateAdd function in Access 2010 report

    I have a field called [Lease End Date] and have created a report where I wish to alert staff 90 days prior to this date that their computer will be up for a change over, so as to make all the required preparations to hand that pc back and be issued with another.

    In this report, I have created an unbound object called End Date and it's Control Source is =DateAdd(("d"),-90,[Lease End Date]). This has worked perfectly until it somehow started dropping the desired result for the #NAME? result.

    I have tried various remedies, like ensuring the [Lease End Date] is correct and referenced to the right query, but nothing works unless I delete and re-enter the complete =DateAdd(("d"),-90,[Lease End Date]) string. Also, this seems to happen on re-opening the database.

    Its driving me bonkers...

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is the field [Lease End Date] referenced as the control source in another textbox? I have run into this before with reports. Sometimes they seem to insist a field used in an expression be referenced elsewhere on the report. If you do already have a textbox bound to that field, try naming the textbox different from the field (like tbxEnd) and using the textbox name in the expression.

    Shouldn't hurt but parens are not needed around "d". Also, day is default unit in date calculation so this should work as well: =[Lease End Date])-90
    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.

  3. #3
    sljedwards is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    5
    Thanks June7, but the DateAdd function does need the "" around the d for Day, as it treats it like an unknown field otherwise and you get the #Type! error. I am trying out renaming the text boxes and will let you know if this resolves it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I know it needs the quotes, I said doesn't need the parens.
    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.

  5. #5
    sljedwards is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    5
    Hi June7... Well that didn't work. Even adding a test textbox with =[Lease End Date]-90 fails on re-opening the database. I am actually after a way to show a date using weekdays between the dates. I just can't figure why my formula works when the database is open in a session, but fails on re-opening...

  6. #6
    sljedwards is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    5
    Ahh. okay June7. I see what you meant. Thanks for the explanation. I will try a few other things and leave out the parenthesis.

    Cheers

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  8. #8
    sljedwards is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    5
    Hi June7...Well, I deleted the entire database and started from scratch and repeated the exact same function and it now seems stable. I don't understand it, but am grateful for your assistance and will put this one down to a glitch in the matrix.

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

Similar Threads

  1. Replies: 5
    Last Post: 06-26-2013, 02:29 PM
  2. Replies: 5
    Last Post: 06-19-2012, 03:16 AM
  3. Replies: 0
    Last Post: 03-11-2012, 09:19 AM
  4. Replies: 2
    Last Post: 11-04-2011, 02:45 AM
  5. Replies: 1
    Last Post: 02-22-2011, 06:10 AM

Tags for this Thread

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