Sum Where Date is less than certain date

[nybaseball22]

I might be getting too technical for my level, but here is what I am
trying to do. I want to sum amounts from a query that is not the
source for my form. I have the sum working, but I want to break it
down. I am trying to sum the numbers that are less than the date in
the date field of my form. Here is what I have so far, courtesy of
help I received in this group:

=IIf([Car] Is Not Null,IIf([Car]="1",DSum("[Miles on Previous
Tank]","AutosGasStats1")/DSum("[Gallons
Pumped]","AutosGasStats1"),DSum("[Miles on Previous
Tank]","AutosGasStats2")/DSum("[Gallons Pumped]","AutosGasStats2")),"N/
A")

This is working on my form for the total amount in my query. Can I
break it down so it only sums the amounts with dates less than the
date on my form (Field name is [Date] in form and query)?

Thanks for the help.

 

[Stuart McCall]

I might be getting too technical for my level, but here is what I am
trying to do. I want to sum amounts from a query that is not the
source for my form. I have the sum working, but I want to break it
down. I am trying to sum the numbers that are less than the date in
the date field of my form. Here is what I have so far, courtesy of
help I received in this group:

=IIf([Car] Is Not Null,IIf([Car]="1",DSum("[Miles on Previous
Tank]","AutosGasStats1")/DSum("[Gallons
Pumped]","AutosGasStats1"),DSum("[Miles on Previous
Tank]","AutosGasStats2")/DSum("[Gallons Pumped]","AutosGasStats2")),"N/
A")

This is working on my form for the total amount in my query. Can I
break it down so it only sums the amounts with dates less than the
date on my form (Field name is [Date] in form and query)?

Thanks for the help.

Dim strCriteria As String

strCriteria = "Date < #" & Me.Date & "#"

=IIf([Car] Is Not Null,IIf([Car]="1",DSum("[Miles on Previous
Tank]","AutosGasStats1", strCriteria)/DSum("[Gallons
Pumped]","AutosGasStats1", strCriteria),DSum("[Miles on Previous
Tank]","AutosGasStats2", strCriteria)/DSum("[Gallons
Pumped]","AutosGasStats2", strCriteria)),"N/
A")

However, before you try this, you would be well advised to change the name
of the field from Date (which is a reserved word in Access) to eg GasDate.
It will save some confusion later on.

 

[nybaseball22]

Thank you Stuart. I am not sure how to change my form criteria with
this code. The string I have is the criteria for a text box. Is
there something I can add that will do the same on the form without
having to change to an expression? I am not very good with that.

Thanks.

 

[Stuart McCall]

Thank you Stuart. I am not sure how to change my form criteria with
this code. The string I have is the criteria for a text box. Is
there something I can add that will do the same on the form without
having to change to an expression? I am not very good with that.

Thanks.

Just prefix the expression with:

Me.TextboxName

IOW:

Me.TextboxName = Iif(([Car] Is Not Null, etc.

(replace TextboxName with your actual control name of course)

 

[nybaseball22]

I'm Sorry Stuart. I am still having some trouble with this. Where
exactly do I add this to my string? Nothing seems to be working.

Sorry for all the confusion.

Thanks

 

[Stuart McCall]

I'm Sorry Stuart. I am still having some trouble with this. Where
exactly do I add this to my string? Nothing seems to be working.

Sorry for all the confusion.

Thanks

Ok let's clarify. when you said:

Is there something I can add that will do the same on the form without
having to change to an expression?

The code you posted is exactly that - an expression. Does this reside in the
ControlSource property of your textbox or in the form's module? I took it to
mean it was in the module and posted the appropriate code.

If it's in the ControlSource, try this:

1. Open the form in design view and select the textbox.
2. Drop down the View menu and choose 'Code'. (you're now looking at the
form's module)
3. Paste in the following Function (I'm going to assume you've changed the
field and control names as I recommended):

Function GasTotals()

Dim strCriteria As String

strCriteria = "GasDate < #" & Me!GasDate & "#"

GasTotals = IIf([Car] Is Not Null,IIf([Car]="1",DSum("[Miles on Previous
Tank]","AutosGasStats1", strCriteria)/DSum("[Gallons
Pumped]","AutosGasStats1", strCriteria),DSum("[Miles on Previous
Tank]","AutosGasStats2", strCriteria)/DSum("[Gallons
Pumped]","AutosGasStats2", strCriteria)),"N/A")

End Function

The 'GasTotals =' line should be all on one line despite how it looks here.

4. Switch back to form design view and replace the textbox's ControlSource
property with:

=GasTotals()