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

    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
    Posts
    5,442
    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:
    Code:
    SELECT COUNT(*) 
      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:
    Code:
    SELECT COUNT(*)
      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):
    Code:
    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#
            Do
                .AddNew
                !Cal_Date = varDate
                .Update
                varDate = DateAdd("d", 1, varDate)
            Loop Until varDate = #1/1/2051#
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    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
  •