DSum and Between

[thart21]

Could someone please point out what could be wrong with this code?

I have a form with txtCutInDate and txtEndDate. I am trying to do a DSum from a query to calculate a volume based on between the two dates and place that volume in the [prodvolume] field, i.e. if my cut in date is 5/1/2007 I need to sum up the volumes from 5/1/2007 through 12/1/2007 and place that in the [prodvolume] field.

1. If I create a standard query and run it with a macro through a cmd button on the form, the query runs correctly based on the chosen dates.

2. Running VBA behind a cmd button (the way I want to do it) always starts calculating with 1/1/2007 no matter what cut in date (beginning date) is chosen, but, it will change the end date based on "txtenddate", i.e. if I choose between 1/1/2007 and 6/1/2007 I get the correct volumes, but, between 3/1/2007 and 6/1/2007 it stills starts at 1/1/2007.

This ran perfectly on another form and I cannot figure out what could be wrong. I have verified field names and all are correct.

Would appreciate any help and a keen eye to see a problem with my code.


Private Sub cmdCalcSavings_Click()
If Not IsNull(Me.Text354) Then
Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [monthdate] Between " & Forms!Projects!txtCutInDate & " And Forms!Projects!txtEndDate")

ElseIf IsNull(Me.Text354) Then
Me.prodvolume = DLookup("[volume]", "tblProjectDetails", "[projectid] = " & Forms!Projects!projectid & "")
If Me!otherloc = "Supplies forecast" Then
Me.prodvolume = DSum("[volume]", "qryMonthlySupplies", "[product] = '" & Forms!Projects!currentpn & "'" & " And [monthdate] Between " & Forms!Projects!txtCutInDate & " And Forms!Projects!txtenddate")
End If
End If

End Sub

 

[pbaldy]

You were doing so well concatenating in form references with literal strings, and then stopped at the end. Further, if that's really a date field, the values will need to be surrounded by "#".

 

[thart21]

Thanks for the reply however, I'm not understanding your suggestion. First, the code works as I described without # around the dates and, second, how do I put #'s around the txtcutindate and txtenddate fields? It gives me an error. Also, forgot to mention a couple of things. The "Supplies forecast" line works perfectly and, if I enter in a specific product it works, i.e. instead of "If Not IsNull [fcstloc] I put If [fcstloc] = ProdOne.

Also, your line about concatenating and then stopping at the end I don't get, could you please elaborate?

Thanks a lot for the help.

 

[pbaldy]

...& " And Forms!Projects!txtenddate")

should be

...& " And " & Forms!Projects!txtenddate)

 

[thart21]

Thank you so much!!! Got it!

Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [monthdate] Between " & "#" & Forms!Projects!txtCutInDate & "#" & " And " & "#" & Forms!Projects!txtEndDate & "#")

 

[pbaldy]

Excellent! It's probably just personal preference (though there's probably a small performance impact), but I'd get rid of the extra concatenations. For instance these instances of yours/mine:

Forms!Projects!product & "'" & " And [monthdate]
Forms!Projects!product & "' And [monthdate]

or

& " And " & "#" & Forms!Projects!txtEndDate
& " And #" & Forms!Projects!txtEndDate