Results 1 to 8 of 8
  1. #1
    nlkehl is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2014
    Posts
    7

    #ERROR when subtracting dates


    I'm trying to find the # of days between 2 dates. I built a query with an iif statement to create the start and end dates according to dates that are in the original table - if the start date is before or equal to 7/27/13, make the date 7/27/13, if not, keep the start date as is. Same concept for the end date.
    I am not trying to build a query that will look at the dates in the query created above and subtract start date from end date: ActiveMonths: ([qryActiveECNsBiomed]![EndDate]-[qryActiveECNsBiomed]![StartDate])
    Every line comes back as #Error. My first thought was that I can't perform a calculation on calculated fields so I made the first query above, a make table query so I could query off of the table. It didn't help.

    Any ideas?

  2. #2
    nlkehl is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2014
    Posts
    7
    Sorry, that should say, I am now trying...

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Often times, that is a sign that one (or both) of the values you are trying to subtract are not formatted as dates (but rather as text).
    Can you post the formulas for the underlying date field calculations?

  4. #4
    nlkehl is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2014
    Posts
    7
    EndDate: IIf([qryPartialPeriodActiveECNsBiomed]![DeleteDate]<41845,[qryPartialPeriodActiveECNsBiomed]![DeleteDate],"7/25/2014") --> Every line errored out.
    So I changed the "7/25/2014" to "41845" --> Any line that has the number version of the date for both enddate and startdate will calculate correctly. But any line that brings over the startdate or enddate as it is in the original table, does not calculate correctly. I find that odd since if I run the query to calculate on the original table, it works fine. Unfortunately, I have to modify the dates to find the lines that fall within a certain date range and the # of active months within that date range.
    I've also tried formatting the columns as general number. It didn't help the lines that have "dates"

  5. #5
    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,716
    Try # around real dates, not "

    #7/25/2014# not "7/25/2014"

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, note that "7/25/2014" will return a Text value of "7/25/2014", not a Date value.
    In addition to the option Orange provided, you can also return a date value of 7/25/2014 using either of the two following formulas:
    DateSerial(2014,7,25)
    DateValue("7/25/2014)


    So you need to make sure that you apply this logic to ALL of your date calculations.
    How is [qryPartialPeriodActiveECNsBiomed]![DeleteDate] calculated?

    If you are looking for months difference, you may want to consider using the DATEDIFF function. See: http://www.techonthenet.com/access/f...e/datediff.php

  7. #7
    nlkehl is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2014
    Posts
    7
    Thank you so much!

  8. #8
    nlkehl is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2014
    Posts
    7
    [qryPartialPeriodActiveECNsBiomed]![DeleteDate] is provided in the data dump that I'm uploading into Access. I have changed all of my queries and they appear to be working correctly. Thank you both for your help. I'll definitely check out DATEDIFF and I'm sure I'll have more questions. I'm building a huge database tool for work and have minimal Access experience.

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

Similar Threads

  1. Error subtracting
    By Derrick T. Davidson in forum Queries
    Replies: 4
    Last Post: 07-25-2013, 01:18 AM
  2. Adding and subtracting dates from current date
    By brianmcleer in forum Access
    Replies: 1
    Last Post: 05-07-2013, 05:15 PM
  3. Subtracting Dates in Queries
    By seanpbent in forum Access
    Replies: 1
    Last Post: 02-01-2013, 04:28 PM
  4. Adding/Subtracting
    By JayX in forum Access
    Replies: 1
    Last Post: 12-15-2011, 01:47 PM
  5. Filtering Dates Error!
    By emilyrogers in forum Forms
    Replies: 3
    Last Post: 02-15-2011, 03:00 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