Min and Max for Date Range in Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!
 
Well, the expressions are incorrect. You must pass STRINGS as arguments for
DMin and DMax.

DMin("intMileage","qryCarStatsRpt")

In a report, you might be better off using the following for control sources.
Especially since you say there can be multiple cars involved.
=Min([IntMileage])
and
=Max([IntMileage])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

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!!
Click to expand...
 
Well, the expressions are incorrect.  You must pass STRINGS as arguments for
DMin and DMax.

    DMin("intMileage","qryCarStatsRpt")

In a report, you might be better off using the following for control sources.
  Especially since you say there can be multiple cars involved.
=Min([IntMileage])
and
=Max([IntMileage])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...


Thanks, John!!! You were right! I checked the double quotes on the
field name and I also changed my iif statement to evaluate the cost
type:

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

That works in the car cost type header perfectly!!!
 
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:

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
-------
(End Code)


For some reason that date range works for all the cars chosen and for
all amounts except this min and max calcs. 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!
 
To get the values for the date range and a specific car using the DMIN and
DMAX you would have to pass in the criteria you are creating in strCriteria.
Since those criteria are NOT present in the base query of the report.

=IIF([txtCarCostType]="Gas",DMax("[intMileage]", "QryCarStatsRpt"), "PKCarID="
& Me.TxtPKCarID & " and DtCostDate ...")

Simpler solution in the report would be to use an expression in a control.
Control Name: ShowMin
Control Source: =Min(IIF(CarCostType="gas",[IntMileage],Null))
and
Control Name: ShowMax
Control Source: =Max(IIF(CarCostType="gas",[IntMileage],Null))

You would do this with controls on the report in the appropriate section
(detail section or a group header or footer). If you do so the value returned
will be for the vehicle that is in that section. If you do this in a group
footer or group header it will be the min and max values for all the vehicles
in the section. That is the mileage for whichever vehicle has the least
mileage will show as MIN and the mileage for whichever vehicle has the most
will mileage (in the period) will show as MAX.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

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:

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
-------
(End Code)


For some reason that date range works for all the cars chosen and for
all amounts except this min and max calcs. 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!
Click to expand...
 
Back
Top