Using DSum

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

My continuos form has 2 fields called detailBeginTime and detailEndTime. I
have a calculated control beside it that returns the time duration between
the 2.

After realizing that I can't place a control on form footer that sums the
durations, I've now started to try using DSUM on the actual table with
criteria from the form in FIGURE 1. FIGURE 2 works when doing a simple test
sum of an autonumber field.

What I'm having trouble with in FIGURE 1 is the syntax. Access won't allow
me to use the DATEDIFF expression part.

Any ideas?


FIGURE 1

=DSum("DateDiff("n", [detailBeginTime],
[detailEndTime])","t_downtimeDetails","[dtID] = Forms![f_DataInput]!dtID")



FIGURE 2 (adds an autonumber field for test and it works)

=DSum("[detailID]","t_downtimeDetails","[dtID] = Forms![f_DataInput]!dtID")
 
Scott said:
My continuos form has 2 fields called detailBeginTime and detailEndTime. I
have a calculated control beside it that returns the time duration between
the 2.

After realizing that I can't place a control on form footer that sums the
durations, I've now started to try using DSUM on the actual table with
criteria from the form in FIGURE 1. FIGURE 2 works when doing a simple test
sum of an autonumber field.

What I'm having trouble with in FIGURE 1 is the syntax. Access won't allow
me to use the DATEDIFF expression part.

FIGURE 1

=DSum("DateDiff("n", [detailBeginTime],
[detailEndTime])","t_downtimeDetails","[dtID] = Forms![f_DataInput]!dtID")
Click to expand...

That should work if you double up the quotes inside quotes:
=DSum("DateDiff(""n"",[detailBeginTime],[detailEndTime])","t_downtimeDetails","[dtID]
= Forms![f_DataInput]!dtID")

But, I think think that is unnecessary, you can use Sum in
the footer section. The aggregate functions operate on
fields in the form's record source table/query, not on
form/report controls. Try using:

=Sum(DateDiff("n", [detailBeginTime], [detailEndTime]))
 
Back
Top