DSum between dates

[nybaseball22]

I have a challenging question here. I am trying to work with values
between certain dates, and want to know if there is some code I can
add that will sum values between certain dates, but not take into
account the year. I want to find averages for seasons, but not
seperate the years. So every year the averages would calculate with
the previous year. Here are my details:

subForm name: AutosGasDataCRVsub
Opens on Main form: AutomobileGasolineSearch
Name of text box where the value will be: AVGMPGWinter
Name of query where information is found: AutosGasStatsCRV

I will have seperate text boxes for each season with the following
dates:

Winter - Nov 15-Mar1
Spring - Mar 1-May31
Summer - June 1-Aug 31
Fall - Sept 1-Nov 15

What I have now that does not factor in the information above is:

=DSum("[Miles on Previous Tank]","AutosGasStatsCRV")/DSum("[Gallons
Pumped]","AutosGasStatsCRV")

Thanks for the help.

 

[Dale Fye]

You might want to write a little function to wrap around the DSUM( )
function.

Public Function fnMpgBySeason(Season as string) as double

Dim strCriteria as string
Dim dblMiles as double, dblGallons as double

'This select statement uses the dates you perscribed for the season
'start and end dates. Realistically, I think I would put these values
'in a table so that I could change them at a later date without having
'to update the code. Then, I would write a query that uses the dates
'in the table. But for now I'll do it this way.

'Because I don't know whether you [PurchaseDate] field contains
'only the date portion of the date, I have added one day to the end
'day of each period. If your data is purely date, you can change these
'ending dates to their correctly formatted 'mmdd' format
Select Case Season
Case "Winter"
strCriteria = "BETWEEN '1115' AND '1232' OR BETWEEN '0101' AND
'0302'"
Case "Spring"
strCriteria = "BETWEEN '0301' AND '0532'"
Case "Summer"
strCriteria = "BETWEEN '0601' AND '0832'"
Case "Fall"
strCriteria = "BETWEEN '0901' AND '1116'"
Case Else
strCriteria = " = '0000'"
End Select

strCriteria = "Format([yourDateField], 'mmdd') " & strCriteria

dblMiles = DSUM("[Miles on Previous Tank]", "AutosGasCtatsCRV",
strCriteria)
dblGallons = DSUM("[Gallons Pumped]", "AutosGasStatsCRV", strCriteria)

if dblGallons = 0 then
fnMpgBySeason = 999
Else
fnMpgBySeason = dblMiles / dblGallons
Endif

End Function

Are you actually storing data in different tables for each vehicle, or is
[AutosGasStatsCRV] a query name?

Assuming it is a query name, you could modify this query to include the
vehicle and pass it a string ("CRV", "Explorer", ...) that you could build
in code to point to the corrrect query.

The declaration might look like:

Public Function fnMpgBySeason(Season as String, Vehicle as String)

and the DSUM statement might look like:

DSUM("[Miles on Previous Tank]", "AutosGasStats" & Vehicle, strCriteria)

HTH
Dale

I have a challenging question here. I am trying to work with values
between certain dates, and want to know if there is some code I can
add that will sum values between certain dates, but not take into
account the year. I want to find averages for seasons, but not
seperate the years. So every year the averages would calculate with
the previous year. Here are my details:

subForm name: AutosGasDataCRVsub
Opens on Main form: AutomobileGasolineSearch
Name of text box where the value will be: AVGMPGWinter
Name of query where information is found: AutosGasStatsCRV

I will have seperate text boxes for each season with the following
dates:

Winter - Nov 15-Mar1
Spring - Mar 1-May31
Summer - June 1-Aug 31
Fall - Sept 1-Nov 15

What I have now that does not factor in the information above is:

=DSum("[Miles on Previous Tank]","AutosGasStatsCRV")/DSum("[Gallons
Pumped]","AutosGasStatsCRV")

Thanks for the help.