Calculated Fields
DMax

I am interested in this too.  I will rewatch both of these videos when I have enough signal to do so, but I can't recall seeing in there how to make it calculate by subtracting the previous record from the current one to determine how many miles were driven between entries. Thank You, Simon, for asking my question for me.  Thank you Kevin for the answers!

This could be quite involved.  At first glance, the solution is simply to subtract a car's previous date's mileage from its current date's mileage to get the total miles driven for that day.  But that only works if a car is only driven only once per day.  If a car is driven multiple times a day, and every one of those trips is logged with date/time and mileage in your table, then the job becomes more complicated: you need to look up the mileage after the previous date's FINAL trip of that car, then look up the mileage after its current date's FINAL trip for the day, and calculate the difference between them.

Whether a car is driven once per day or multiple times a day, we need to look up a car's logged mileage after its "final" trip of a day.  The final trip of a date is marked by highest date/time value of that day, so you need to look that up first:

    Dim LookupDate As Date, LoggedDateTime1 As Variant
    LookupDate = #12/18/2022#    ' Date entered by the user to look up daily mileage for.
    LoggedDateTime1 = DMax("[DateTimeOfTrip]", "MyMileageTable", "[CarID] = " & CarID & " AND Int([DateTimeOfTrip]) = Int(#" & Format(LookupDate, "General Date") & "#)")
    
In the above, I assume your table tracks multiple cars that are identified by some kind of car IDs.  The Int() is used above to extract only the month, day, and year of the two dates, not the time.  In Access, a date/time is stored in a floating point number like this: 12345.56789; the date value is represented by the integer before the decimal point, and the time value is the fractional value after the decimal point.  The Int() function returns only the "integer" portion before the decimal point, thus only the date portion.  In short, the above DMax() looks up the max date value of the month, day, and year specified by the user.

If your table looks like below, and the user enters 12/18/22 as the lookup date:

    DateTimeOfTrip    MileageLogged
    12/17/22 4pm      24286    
    12/17/22 7pm      24288
    12/18/22 9am      24289
    12/18/22 2pm      24300
    12/18/22 4pm      24302
    12/18/22 11pm      24307
    12/19/22 3pm      24309
    12/20/22 11am      24311
    
Then the above DMax() returns 12/18/22 11pm as the final trip of 12/18/22.

One caveat: if the user enters a nonexistent date (i.e. a date when no car was driven and no entry was logged in the table), LoggedDateTime1 above will get a null value.  If so, the job ends here, and you return a negative result to the user.  But if the user has entered a date that actually had a car driven, proceed as shown below.

The next step is to look up the exact logged date/time of the previous date's final trip.  Here I use 12:00:00am to mark the beginning of a date, although some businesses may use different times for that.  Also, "previous date" is not necessarily 1 day less than the current date, because a car may not be driven in consecutive days.  So here comes another tricky function:

    Dim LookupPreviousDate As Date, LoggedDateTime2 As Variant
    LookupPreviousDate = Int(LookupDate - 1) + #11:59:59pm#
    LoggedDateTime2 = DMax("[DateTimeOfTrip]", "MyMileageTable", "[CarID] = " & CarID & " AND [DateTimeOfTrip] <= #" & Format(LookupPreviousDate, "General Date") & "#")
    
The logic behind the above is that, to look up the previous logged date/time before 12/18/22, you need to look up the max date/time value that is less than 12/17/22 11:59:59pm.  I subtract one from 12/18/22 11pm, yielding 12/17/22 11pm.  I use Int() again to make it 12/17/22.  Then I add the literal value of 11:59:59pm, finally yielding 12/17/22 11:59:59pm.  The DMax() above looks up the date right before or equal to 12/17/22 11:59:59pm, and that is your "previous date/time" logged in your table when the same car was driven.

So now we have the values of the two key dates: LoggedDateTime1 stores the logged date/time of the final trip of the date specified by the user, and LoggedDateTime2 stores the previous day's final trip's logged date/time.

We then need to look up the mileages logged for those two dates, then calculate the difference to yield the number of miles driven for the user's specified date.  

Another caveat: LoggedDateTime2 may also be null.  If a car has only been driven once, then it doesn't have a "previous trip," and LoggedDateTime2 will get the null value in the code above.

If LoggedDateTime2 is null, that car doesn't have a previous trip, and that means the starting mileage of that car is zero.  With all the above info, the code to finally accomplish our goal is:

    Dim Mileage1 As Long, Mileage2 As Long, MileageForLookupDate As Long
    
    Mileage1 = DLookup("[MileageLogged]", "MyMileageTable", "[CarID] = " & CarID & " AND [DateTimeOfTrip] = #" & Format(LoggedDateTime1, "General Date") & "#")
    
    If IsNull(LoggedDateTime2) Then
        Mileage2 = 0
    Else
        Mileage2 = DLookup("[MileageLogged]", "MyMileageTable", "[CarID] = " & CarID & " AND [DateTimeOfTrip] = #" & Format(LoggedDateTime2, "General Date") & "#")
    End If
    
    MileageForLookupDate = Mileage1 - Mileage2


Sorry for the long post.  Things could be a lot simpler if only one car is used and it is driven only once per day.  But I include all the possibilities necessary for you to consider.  The code above would also work for one car and one trip per day, or any number of cars and any number of trips per day per car.

If you store both the starting mileage and ending mileage of each trip in every log entry (which I recommend), it may be easier to calculate the number of miles for a day.  That would be a (slightly) easier task, and I'll save it for another post.

Thank you so much!  I think I will need to rethink the best way to log milage and figure miles.

Thank you for taking the time to help this helps alot

You are welcome both.  My previous post has an error.  If a car has only made one trip, then its starting mileage was not zero; it was the mileage shown on the car's dashboard right before it made its first trip.

If I included a beginning milage field, how hard would it be to make Access automatically pull the previous ending milage into the beginning milage of the next record?

Hi Andrea, to find the beginning mileage, you need to look up the previous trip's ending mileage.  The previous trip could be several days ago, a day ago, or just a few minutes ago if we use my earlier assumption that a car can have multiple trips per day.  The use of DLookup() and DMax() here would be less tricky.  You only need to look up the date and time of the trip that was immediately before the current trip, on whichever day.  In VBA terms, the previous trip would be the one with the maximum date/time value that is less than the date/time of the current trip:

    Dim CurrentDateTime As Date, PrevDateTime As Variant, PrevMileage As Long
    CurrentDateTime = #12/26/2022 1:36pm#
    PrevDateTime = DMax("[DateTimeOfTrip]", "MyMileageTable", "[CarID] = " & CarID & " AND [DateTimeOfTrip] < #" & Format(CurrentDateTime, "General Date") & "#")
    PrevMileage = DLookup("[MileageLogged]", "MyMileageTable", "[CarID] = " & CarID & " AND [DateTimeOfTrip] = #" & Format(PrevDateTime, "General Date") & "#")

Of course, you can also get the starting mileage by having the driver read it from the dashboard before every trip.

Thank you, I have to admit, I have a very hard time with dates as criteria, so I would probably fudge this all up...lol  I can't seem to figure out how to translate these vba codes into my own databases if they include date values....I have a lot to learn still.  I guess, until I can work on learning about dates as criteria, I will just have to set it up so that the beginning milage will need to be submitted for each trip, just as the ending milage.  

Thank you again.