Try using these three queries. It pulls last months mileage even if there
is no entry on first or last day of the month.
Renaesout_Start ---
SELECT Renaesout.[Vehicle Number], Min(Renaesout.[Start Mileage]) AS Start,
Min(Renaesout.TripDate) AS [Start Date]
FROM Renaesout
WHERE (((Renaesout.TripDate)>=DateAdd("m",-1,Date()-Day(Date())+1)))
GROUP BY Renaesout.[Vehicle Number]
ORDER BY Renaesout.[Vehicle Number], Min(Renaesout.[Start Mileage]) DESC;
Renaesout_End ---
SELECT Renaesout.[Vehicle Number], Max(Renaesout.[End Mileage]) AS [End],
Max(Renaesout.TripDate) AS [End Date]
FROM Renaesout
WHERE (((Renaesout.TripDate)<=Date()-Day(Date())))
GROUP BY Renaesout.[Vehicle Number]
ORDER BY Renaesout.[Vehicle Number], Max(Renaesout.[End Mileage]) DESC;
Renaesout_Mileage ---
SELECT Renaesout_Start.[Vehicle Number], Renaesout_Start.[Start Date],
Renaesout_Start.Start, Renaesout_End.[End Date], Renaesout_End.End
FROM Renaesout_Start INNER JOIN Renaesout_End ON Renaesout_Start.[Vehicle
Number] = Renaesout_End.[Vehicle Number];
--
KARL DEWEY
Build a little - Test a little
Renaesout via AccessMonster.com said:
Hello!
I have a table set up with Date, Vehicle Number, Start Mileage and End
Mileage.
I want to be able to pull up the start mileage on the 1st of the month and
the end mileage on the last day of the month.
I have read through other posts and can't seem to put my finger on what I'm
looking for.
Thank you in advance for the help!!