Results 1 to 10 of 10
  1. #1
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44

    Using Datediff and one of the date fields are NULL?

    Hi.



    I have to find the average between two date fields, realizing I have NULL values See example below:

    SELECT AVG(DATEDIFF(DAY,DATEvalue1, DATE_value2))
    FROM TABLE2


    NULL VALUES AT DATE_VALUE2.


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    youd need to protect against null.
    either only query on non-nulls
    or
    build a big function to test for null in both fields to prevent errors.

  3. #3
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    Examples? Please?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a query/view "SELECT * FROM TABLE2 WHERE DATEvalue1 IS NOT NULL AND DATEvalue_2 IS NOT NULL;" save it as qryValidDates
    Now us your formula SELECT AVG(DATEDIFF(DAY,DATEvalue1, DATE_value2)) FROM qryValidDates

    Cheers,
    Vlad

  5. #5
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    Thanks sir.
    It worked. One last item. My user would like the data for this past year. How would that look?

    Let's still use my example:
    SELECT AVG(DATEDIFF(DAY,DATEvalue1, DATE_value2))
    FROM TABLE2
    Thanks.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you want to restrict datevalue1 to last year you can modify the first query as follows:

    "SELECT * FROM TABLE2 WHERE DATEvalue1 BETWEEN #1/1/2017# AND #12/31/2017# AND DATEvalue_2 IS NOT NULL;"

    Cheers,
    Vlad

  7. #7
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    Hi Vlad, would I still be able to use the AVG function with your response above?

    perhaps:
    "SELECT * FROM TABLE2 WHERE AVG(DATEVALUE_1 BETWEEN #1/1/2017# AND #12/31/2017# AND DATEVALUE_2 IS NOT NULL);"

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Quote Originally Posted by 9944pdx View Post
    Hi Vlad, would I still be able to use the AVG function with your response above?

    perhaps:
    "SELECT * FROM TABLE2 WHERE AVG(DATEVALUE_1 BETWEEN #1/1/2017# AND #12/31/2017# AND DATEVALUE_2 IS NOT NULL);"
    not really, I think what you want is SELECT AVG(DateDiff(DateValue1,DateValue2) FROM Table2 Where DateValue1 BETWEEN #1/1/2017# AND #12/31/2017# AND DATEVALUE_2 IS NOT NULL;

    Cheers,
    Vlad

  9. #9
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    When datevalue1 is NULL, you can substitute it with the current date, if this is acceptable you can make the average over all records, NULL or not with the following SQL statement (assuming you want the number of days between the 2 dates, otherwise substitute the d argument with the correct interval)

    select avg(datediff(d,coalesce(datevalue1,getdate()),coalesce(datevalue2,getdate())))as DaysDiffer from Tablename

  10. #10
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    This worked, slow feedback sorry.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-16-2016, 05:02 PM
  2. Replies: 2
    Last Post: 10-01-2015, 10:10 AM
  3. Summing DateDiff Fields
    By 1Christmas2 in forum Queries
    Replies: 3
    Last Post: 09-29-2013, 01:57 PM
  4. DateDiff to include start date
    By AussieGal in forum Access
    Replies: 1
    Last Post: 05-07-2013, 02:50 PM
  5. Replies: 1
    Last Post: 02-25-2011, 06:11 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