Report with Min and Max for a given date range? 1

[misscrf]

I have a query of costs for cars. The cars are given a number and a status for current or retired. Each car has a make and model.

For each car there are car costs. The cost types are gas, oil change, body work. Whenever gas cost is entered, the mileage is entered, the gallons of gas purchased and the total cost.

The report I am working on gives the users a menu to choose 1 or more cars (multi-select list box) and any kind of scenario for a date range (month drop down for 1 month, 2 fields for a range, or one of them for <= or => ).

This reports each car, grouped with the car costs under it by type.

The only thing I am missing is the average mileage for the time period that is queried. In order to get this, I need to get the min and max mileage for the queried date range. I have the following control source for the report:

SELECT tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, tblCarCost.CurCostAmount, tblCarCost.intMileage, IIf([txtCarCostType]="Gas",[intGallons],Null) AS MyGallons, tblCarCost.PKCarCostID, tblCarCost.FKCostType, IIf([tblCarCost]![FKCostType]<>6,Sum([tblCarCost]![CurCostAmount]),0) AS CostNoBod

FROM (tblCarStatus RIGHT JOIN (tblCarNum RIGHT JOIN (tblCarModel RIGHT JOIN (tblCarMake RIGHT JOIN tblCar ON tblCarMake.PKCarMakeID = tblCar.FKCarMake) ON tblCarModel.PKCarModelID = tblCar.FKCarModel) ON tblCarNum.PKCarNumID = tblCar.FKCarNum) ON tblCarStatus.PKCarStatusID = tblCar.FKCarStatus) LEFT JOIN (tblCarCostType RIGHT JOIN tblCarCost ON tblCarCostType.PKCarCostTypeID = tblCarCost.FKCostType) ON tblCar.PKCarID = tblCarCost.FKCar

GROUP BY tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, tblCarCost.CurCostAmount, tblCarCost.intMileage, IIf([txtCarCostType]="Gas",[intGallons],Null), tblCarCost.PKCarCostID, tblCarCost.FKCostType;

I have 2 unbound fields on the report.

MinMile: =IIf([intMileage] Is Null,"",DMin([intMileage],"QryCarStatsRpt"))

MaxMile: =IIf([intMileage] Is Null,"",DMax([intMileage],"QryCarStatsRpt"))

I get the min for both. I have put these controls in the cost type header and in the report footer. No difference.

Can anyone give my some guidance as to how to get the min and max mileage for any given date range?

Thanks!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Are you looking for the average mileage by car? If so, you should be able to add text boxes to a car group header or footer section with control sources like:

=Min([intMileage])
=Max([intMileage])

You Dmin() and DMax() don't filter by car or date that I can see.


Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you for responding.

On the report, I have a header grouping and footer grouping for each car. The report is run by a report menu form where the user chooses the car or cars and then provides the date criteria. The command button that launches the report launches it selecting the report control source where [form cars] and [form dates] match.

It does the filtering right, but the dmin and dmax only show right for the min. the max shows the min too.

I will try your suggestion of just adding those text boxes to my header section, but I am pretty sure I already tried that one way way back a long long time ago. I will see what the results produce so I can let you know how that behaves.

Any other thoughts? Is this a simple enough thing to do? I only ask because I did my diligent searching before posting this question and I didn't find a lot. I don't know if my search was bad but I kept getting hits for min and max button on the report instead of this topic lol.

I am trying to get average mile by car, to answer your question. Among some other calcs. (total mileage for time range as well).


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

1) IMO, an IIf() expression should never return more than one possible data type. Your IIf() might return a number or a string.
2) You didn't tell us where the "unbound fields on the report" are located (which section)
3) You don't suggest if the SQL statement is the "QryCarStatsRpt"
4) We don't know how you apply the criteria to the report

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you so much, dhookom. You got me thinking. Especially about the grouping level and the dmin. I checked and the field is in the car cost type header. It is only relevant for the gas cost type.

I now have this:

=IIF([txtCarCostType]="Gas",DMax("[intMileage]","QryCarStatsRpt"),"")

and it works!

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

grr. I spoke too soon. It is giving me the min and max mileage, but for the car. Not within the date range that is being filtered from the form. The form has the following code:

Private Sub cmdOpenReport_Click()
On Error Resume Next

Dim strCriteria As String
Dim strReport As String

strReport = "rptCarStats"

strCriteria = "1=1 "
strCriteria = strCriteria & _
BuildIn(Me.lstCar, "[PKCarID]", "")

If Me.ChkYearToDate = 0 Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
 " AND Year([DtCostDate]) = " & Year(Date) & _
 " AND Month([DtCostDate]) <= " & Month(Date)
End If

If Me.cboMonthYear.Value = "" Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
" AND Month([DtCostDate]) = " & Month(Me.cboMonthYear) & _
" AND Year([DtCostDate]) = " & Year(Me.cboMonthYear)
End If

Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

If Len(Me.dtRangeBegin & vbNullString) = 0 Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
" AND ([DtCostDate]) >= " & Format(Me.dtRangeBegin, conJetDate)
End If

If Len(Me.dtRangeEnd & vbNullString) = 0 Then
strCriteria = strCriteria
Else
strCriteria = strCriteria & _
" AND ([DtCostDate]) <= " & Format(Me.dtRangeEnd, conJetDate)
End If

DoCmd.OpenReport strReport, acViewReport, , strCriteria

If Err = 2501 Then Err.Clear
End Sub

For some reason that date range works for all the cars chosen and for all amounts except this min and max :-(. I think I need to set the control source of the report to that query here in the code.

Does that sound right? Let me know if I am off base, please.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Go back and read my first reply. I told you it would not work.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

This part?

You Dmin() and DMax() don't filter by car or date that I can see.

I get that the fields are not doing the filtering. I have the query set up and then I have the code on the click of a report menu form to handle the strCriteria filter.

Is that not a valid method?

I appreciate your help and all your experience with this!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The where condition of the DoCmd.OpenReport will not have any effect on the DMin() or DMax(). That's why I expected you to try my suggestion from 18 Apr 11 14:17.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

ok. I will try that this evening and let you know how it goes. Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I don't know how you have the patience for it Duane, but you were so right!!! Thanks for sticking with me. It works just like it should.

Important note for people as dense as me. Simple min and max pulling value from source query. The min and max are formulas in controls in the ID header, so that they report by ID.

Works like a charm!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

So I took it back to the users and it turns out this is not "exactly" what they need :-(

For the mileage, they need the mileage to grab from before the given range. Let me explain...

date cost type cost mileage
1/1/2011 gas $42.00 12,000
1/10/2011 gas $38.00 14,000
1/17/2011 oil change $35.00
1/25/2011 gas $48.00 16,724
2/8/2011 body work $300.00
2/9/2011 gas $35.47 19,345
2/18/2011 gas $49.00 23,912
2/27/2011 gas $42.00 25,754


If that is our data, then lets say the user is picking the month of February for their report. We then pull the min mileage, which gives us 19,345 (on 2/9/2011) and the max mileage, which gives us 25,754 (on 2/27/2011).

The problem is that to get an accurate cost / per mile for that range of costs, the mileage difference needs to pull the min as 16,724 (from 1/25/2011) or the max mileage before the date range.

Make sense?

I think my head is going to implode...

Please let me know if you have any clue how to go about this. Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

If they want the mileage for February, why don't they just choose a start date of 1/25/2011?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

because they don't want that cost. The mileage from that point (1/25/2011) - the end of feb (2/27/2011) comes into factor with the cost that is added up for Feb. Believe me I asked this, and they said this is what they need.

I find it kind of inefficient, but as they say, the customer is never wrong!

I wish I could find who coined that phrase and throw an application at them.. Literally.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

There are many ways to accomplish this. I would provide a combo box on a form for users to select the from date and possibly the to date. Then the text box on the report can sum the costs where the transaction date > than the start combo box (minimum date).

=Sum([Cost] * Abs([DtCostDate]>Min([DtCostDate]))

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I am not sure I totally understand, but I will try to break down what you have there with my app.

The mileage may not always be the immediately previous date from the range. the previous date may have been a cost type of body work or an oil change or something. It needs to be the previous date that has a mileage, and that can get tricky too.

Someone could add gas twice in the same date. If they travel really far, you could have 2 car costs of gas with different mileage on the same date.

So I will see if I understand what you have there. You are usually right with your suggestions, so I am inclined to trust you.

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would make sure the users selected a date with a mileage.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

but they won't. They will mostly do monthly or yearly reports, which means first and last of month. Chances are not that gas will always be filled on the first and last of every month.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Can I/would it be possible/ how could I ...

Do a dlookup for the max mileage where the date is the max date > the min date of the date range provided in the strcriteria of the query for the report.

Make any sense at all? lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The problem is that to get an accurate cost / per mile for that range of costs, the mileage difference needs to pull the min as 16,724 (from 1/25/2011) or the max mileage before the date range.

Make sense?

Sure, this makes sense. So why pull the minimum mileage from a previous date without pulling all of the costs from that previous date? If you don't pull all the costs then your total costs won't be accurate.

Only provide your users with accurate ranges that include all costs between mileage dates.

Duane
Hook'D on Access
MS Access MVP