Starting mileage on 1st day of month, ending mileage on last day of month

[Renaesout via AccessMonster.com]

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!!

 

[Jeff Boyce]

The structure you described (Date, Vehicle, Start, End) implies that any
date, vehicle, start and/or end can be entered.

What do you mean by "pull up the start mileage on the 1st of the month"?
The way I interpret that, I'd create a query that returns the [StartMileage]
where Day([MileageDate]) = 1.

By the way, the word "Date" is a reserved word in Access. If that is
actually the name of your field, you will only confuse both you and Access
about which "date" you mean. Consider renaming the field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

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];

 

[Renaesout via AccessMonster.com]

Karl,
thank you so much! I have never done the join thing, I learned alot!

Is there a way to set it up so the user gets to pick which month to view, or
which period of time to view, such as Jan 1 - Jan 31, or Jan 1 - Dec 31? LOL
The fun never stops! Thank you again for you help thus far!!

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];

Hello!
I have a table set up with Date, Vehicle Number, Start Mileage and End

[quoted text clipped - 6 lines]

looking for.
Thank you in advance for the help!!

 

[Dale Fye]

This seems like the long way.

If there is program logic that makes sure that End Mileage is always equal
to or greater than Start Mileage, and that Start Mileage is always equal to
or greater than the previous End Mileage, then he should be able to do
something like:

SELECT [Vehicle Number],
Year([DateDriven]) as Yr,
Month([DateDriven]) as Mnth,
MIN([Start Mileage]) as StartMonth,
MAX([End Mileage]) as EndMonth
FROM yourTable
WHERE [DateDriven] BETWEEN DateSerial([What year?], [What month (##)?], 1)
AND DateSerial([What year?], [What month (##)?] + 1, 0)

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

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

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!!