Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013

    Unanswered: Date difference between null fields

    Database results = the time elapsed between last entry

    Field ----- Date Field
    Field ----- Gallons Used

    Query results:

    1/1/2014 50 Gallons
    1/2/2014 Null
    1/3/2014 50 Gallons

    Question: There is 2 days between recording a value(1/1/2014-1/3/2014) How to calculate in a field to count the null days between the last entry.

    Context: I backwash a filter on 1/1 and use 50 gallons. It is 2 days later before I backwash the filter again. The null field represents the day I did nothing.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    If there is a row in the table for every day, whether the filter was backwashed or not, a simple query will provide the answer:
      FROM TableName 
    WHERE (BackwashDate BETWEEN #1/1/2014# And #1/3/2014#) AND
          (GallonsUsed IS NULL);
    If it's not the case, You can use a calendar table to supply a continuous list of dates and a Left Join:
      FROM Tbl_Calendar LEFT JOIN 
           TableName ON TableName.BackwashDate = Tbl_Calendar.Cal_Date
    WHERE (Tbl_Calendar.Cal_Date Between #1/1/2014# And #1/3/2014#) AND 
          (TableName.GallonsUsed Is Null);
    You can create a Calendar table using this procedure (only once):
    Public Sub CreateCalendar()
        Const c_SQL As String = "CREATE TABLE Calendar ( Cal_Date DATETIME NOT NULL );"
        Dim rst As DAO.Recordset
        Dim varDate As Date
        CurrentDb.Execute c_SQL, dbFailOnError
        Set rst = CurrentDb.OpenRecordset("Calendar", dbOpenDynaset)
        With rst
            varDate = #1/1/2000#
                !Cal_Date = varDate
                varDate = DateAdd("d", 1, varDate)
            Loop Until varDate = #1/1/2051#
        End With
        Set rst = Nothing
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jan 2003
    Mind clarifying a couple of things?

    Are your date parameters external inputs (someone types it in) or assumed (like today's date)?

    Is your intent to find the elapsed time from the last backwash, or the time between the last two backwashes?

    Say you had an entry today with a backwash, would the result be zero? Would it be the time between the last two backwash events?

    My initial impression that it is the former since that would be useful if it is being used in some sort of tracking dashboard.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts