dsum on a subform

[scottian]

Im trying to use Dsum on a subform, ive put it in the subform footer
=DSum([Hours],[subform],[rate]=15.5)
but i get an error
any ideas

 

[Eupher]

Enclose the DSum arguments in quotes. i.e.:

DSum("[field]", "table", "[myfield] = 15.5&quot

Also, I don't know if you can use the subform as the domain. Try using the subform's record source instead.

Ken S.

 

[jrbarnett]

Hi,

The syntax for the DSum function is:

=DSum ("Fieldname", "TAblename", "Whereclause&quot

Therefore yours should be:

=DSum("[Hours]","table name","[rate]=15.5&quot

John

 

[scottian]

tried this, but i keep getting an error.
invalid use of null

this is my code
------------------------------
Private Sub Form_Load()
Dim a As Single
Me![Text25] = 0
a = DSum("[Hours]", "SubformData", "[rate]=11.62&quot
'If a <> Null Then
Me![Text25] = a
'Else
'End If
End Sub
------------------------------

can anyone help

 

[jrbarnett]

Hi,

Two points: Firstly, I think the error is due to the result of a being a null value - ie there are no rates of 11.62 in the table.

You can test this by opening the debug window (press Ctrl G) and type:
? DSum(&quot;[Hours]&quot;, &quot;SubformData&quot;, &quot;[rate]=11.62&quot <RETURN>

and see what it says, if &quot;Null&quot; then you have your answer.

John

 

[scottian]

what im trying to do is calculate overtime.

some users have completed overtime in a month, all the db is doing is calculating overtime, so some months none has been done. the main form has a text box with the months of the year in, and as you move from month to month the subform lists all overtime for that month. i then need to sum up the overtime done. the overtime can be done at different rates so the first thing i need to do is to calculate each individual rate and then total those. this is where im getting tied up in knots. Ive managed to work out the dsum for each rate, but if there is a null value, the total of all the rates gives an error, since a calculation cant actually add anything to null. so the pseudo code would be
---------------------------------------------------
on subform open

text1 = dsum of hours where rate is 15.5
if (dsum of hours where rate is 15.5) is null then
text1 = 0
text2 = dsum of hours where rate is 11.5
if (dsum of hours where rate is 11.5) is null then
text2 = 0
text3 = text1 + text2

on move to next record
me requery
---------------------------------------------------
please someone help

 

[Eupher]

Hmm... I just tried it in Access XP and I couldn't reproduce the error message. Try the Nz() function. Something like this might work:

a = Nz(DSum("[Hours]", "SubformData", "[rate]=11.62"), 0)

HTH...

Ken S.