need help selecting betwween dates

[Stephen]

Good day

this works fine except but for a vehicle change March 16

SELECT MIN([start mileage]) AS [Start of year mileage],
MAX([home mileage]) AS [End of year mileage],
MAX([home mileage])-MIN([start mileage]) AS [Total mileage],
SUM([home mileage]-[start mileage]) AS [Total business mileage],
SUM([gas]) AS [Sum Of gas], SUM([US gas]) AS [Sum Of US gas],
SUM([US gas2]) AS [Sum Of US gas2],
SUM([servise $]) AS [Sum Of servise $],
SUM([us cost]) AS [Sum Of us cost],
SUM([gas pur]) AS [Sum Of gas pur],
SUM([insureance]) AS [Sum Of insureance],
SUM([plate]) AS [Sum Of plate]
FROM [travel 2007];

what I need is total mileage which is as posted but Jan1 to March16 to
be added to March16 to end of year

due to vehicle change I have a negative mileage

can any help with where to place between dates for total mileage value

Thank You
Stephen

 

[KARL DEWEY]

The SQL you posted has no dates so it is hard to do a BETWEEN without dates!

 

[Happy]

The SQL you posted has no dates so it is hard to do a BETWEEN without dates!

Thanks Karl

Date is a field in my table but was needed in query.

Do I now add it?
And how do I add to SQL or do I take total mileage out?

thanks
Happy

 

[KARL DEWEY]

Your field should not be named Date as it is a reserved word in Access.

Edit SQL like this ---
FROM [travel 2007]
WHERE [travel 2007].[Date] BETWEEN #3/16/2007# AND #12/31/2007#;

 

[Happy]

Your field should not be named Date as it is a reserved word in Access.

Edit SQL like this ---
FROM [travel 2007]
WHERE [travel 2007].[Date] BETWEEN #3/16/2007# AND #12/31/2007#;

thank you Karl

after many frustrations and my first typing of this email I had a flash

I was trying to do both parts of the year or vehicle
when I did only one it works

So what I think is I now have two reports (one for each vehicle)
hopefully I can now learn sub reports and combine data on a main report
This will run on three years

thank you for your help and patience
Happy (Smiles)
*sent from my temp computer name changed so I can tell computers apart

 

[KARL DEWEY]

Pieces being left out is frustrating!

Assume your vehicle ID field is VehicleID and the changed vehicle number is
'204C.'
Edit SQL like this ---
FROM [travel 2007]
WHERE [travel 2007].[Date] BETWEEN #3/16/2007# AND #12/31/2007# AND
VehicleID = "204C" OR [travel 2007].[Date] BETWEEN #1/1/2007# AND
#12/31/2007# AND VehicleID <> "204C";


--
KARL DEWEY
Build a little - Test a little

Your field should not be named Date as it is a reserved word in Access.

Edit SQL like this ---
FROM [travel 2007]
WHERE [travel 2007].[Date] BETWEEN #3/16/2007# AND #12/31/2007#;

thank you Karl

after many frustrations and my first typing of this email I had a flash

I was trying to do both parts of the year or vehicle
when I did only one it works

So what I think is I now have two reports (one for each vehicle)
hopefully I can now learn sub reports and combine data on a main report
This will run on three years

thank you for your help and patience
Happy (Smiles)
*sent from my temp computer name changed so I can tell computers apart

 

[Stephen]

Thanks Karl

no vehicle ID field just a note in one of the note fields but this has
now been resolved in a new database design

this solves 3 databases over 3 years cleaning up errors

Thank You for your help
Smiles