I am working on a desk-side Access Application that is connected to a SharePoint List that contains Fueling records for a vehicle fleet.
Every record is uniquely identified with a no-duplicates indexed primary key record number [tbl_FuelRecs].[ID]
Every record contains a unique indexed vehicle identification number [tbl_FuelRecs].[PlateID]
Every record contains a numeric entry to capture the vehicle's odometer reading [tbl_FuelRecs].[ODORead]
Every record is time-datestamped at entry via [tbl_FuelRecs].[Created]
Every record is time-datestamped at save via [tbl_FuelRecs].[Modified]
... and a couple other odds and ends that don't pertain to the question.
Every time someone enters a new fuel record they provide the PlateID and the ODORead.
I'm trying to write a DMax query to find the MOST recent PREVIOUS fueling record for that vehicle and return the [ID] of that previous record...
Here's what I'm trying in a single-table (no inner joins, just the single table) Access LookUp Query against [tbl_FuelRecs] where my columns are:
[ID], [PlateID], [ODORead], [LastFuelID: DMax("ID","tbl_FuelRecs","ID<"&[ID] AND "PlateID="&[PlateID])]
It is doesn't work, [LastFuelID] returns an error.
Any ideas or pointers to get me moving in the right direction?