Calculate difference between current and last occurrence

ms1202

New Member
Joined
May 25, 2006
Messages
26
I have a table containing details of each fleet related event. It contains the date, the vehicle registration, mileage, event type (taken from 'fuel', 'check', 'service', 'defect', 'carwash', 'goods'), gallons of fuel, cost, notes.

I want to add a field that calculates the miles per gallon since the last refuelling. To do this I need to be able to find the difference between the mileage of the current record and the mileage when the car was last fuelled and divide the answer by the number of gallons now put in. I'm a bit of a novice with Access and so don't know how to call the record with the highest date less than the current record's date in which fuel was the event type and the registration number was equal to the current record's registration number. Hope that makes sense! If it's the first record I want it to say "First Time".

Now the second bit; I also want to add a field calculating the average daily mileage since the last check. That again means finding the difference between this mileage and the last mileage where the type was check and the also the difference between the dates. Can anybody help?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Need to provide additional information:

You want to save this information in your table? Are you using forms to enter this data? You mention ONE table... are you storing all this data in one table?

Normally, you would let the form, query or report calculate the things you mention. You can store this data, but why? There are times when it's necessary, but it's not the norm.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
ms1202,
Lets just do the "Find the record" first, then after we have the correct record "in-hand" we can work on the rest of the request.
Field names for the example will be: Set current record date to: CurrentDate. vehicle registration will be VehicleRegistration, mileage will be Mileage, event type will be EventType, date will be EventDate. The table name will be tblVehicleEvents.
Now, lets build the criteria that we can use to find the mileage from the last "fuel" event.
Criteria = "VehicleRegistration=sqdq & me!VehicleRegistration & dqsq AND EventDate=#dq & DMax("EventDate", "tblVehicleEvents", "VehicleRegistration=sqdq & Me!VehicleRegistration & dqsq AND EventDate<#dq & CurrentDate & dq#dq) & dq# AND EventType=sqFuelsqdq)
Because single quotes and double quotes can be VERY confusing when put together (ie: '" or "') I have used sq for single quote and dq for double quote in the statement for the Criteria. Every sq needs to be changed to a single quote ( ' ) and evey dq needs to be changed to a double quote ( " ).
If you use this Criteria just built in a DLookup statement, you will get the record you want. So, the DLookup will look like this:
LastMileage = DLookup("Mileage", "tblVehicleEvents", Criteria)
I assume you can handle the subtraction, etc once you get the LastMileage.
Be sure to change all the field names and the table name to the names you actually use before trying this example.
HTH, and after we get this working, then we can proceed to the rest of your request.
When this does not work the first time (they usually don't), be sure to tell us the error message, and include a copy of the code you are using!
 

Forum statistics

Threads
1,137,207
Messages
5,680,194
Members
419,888
Latest member
Prasad K

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top