DSum in Unbound control on form

[Guest]

I'm trying to use DSum to sum the values on a form. The form is called
frmWorksSubform. The field I would like to sum is called LineTotal. The
conditional field is called TransactionTypeID and should = 2.

I have tried setting the control source as:

=DSum("[LineTotal]", "[frmWorksSubform]", "[TransactionTypeID] = 2")

but I just get #error displayed.

If it has to be based on a query or table then I have two conditions,
[TransactionTypeID] = 2 and [TaskID] = [Forms]![frmWorks]![TaskID].

What am I doing wring?

Dave

 

[Guest]

I don't think that will work as a Control Source. If the form is bound to a
record source and other controls are bound and this is just an unbound
control, then I would suggest code in the form's Current Event that will
provide the calculation.

DSum("[LineTotal]", "[frmWorksSubform]", "[TransactionTypeID] = 2")

The first problem with this statement is that you can't use a DSum on a
form. It has to be either a query or a recordset. It appears the form above
is a sub form, so the code should be in the current event of the main form
and based on the same table or query on which the sub form is based. That
will require you use the addtional condition you specified:
[TransactionTypeID] = 2 and [TaskID] = [Forms]![frmWorks]![TaskID

So your Dsum shoud look like this:

DSum("[LineTotal]", "YourTableOrQueryName", _
"[TransactionTypeID] = 2 AND [TaskID] = " & Me.TaskID)

[LineTotal] should be the field in the table or query you want to add up.
If not, change it to the corrent field name.

 

[Guest]

Thanks. I ended up using the IIf function inside a Sum statement.

I don't think that will work as a Control Source. If the form is bound to a
record source and other controls are bound and this is just an unbound
control, then I would suggest code in the form's Current Event that will
provide the calculation.

DSum("[LineTotal]", "[frmWorksSubform]", "[TransactionTypeID] = 2")

The first problem with this statement is that you can't use a DSum on a
form. It has to be either a query or a recordset. It appears the form above
is a sub form, so the code should be in the current event of the main form
and based on the same table or query on which the sub form is based. That
will require you use the addtional condition you specified:
[TransactionTypeID] = 2 and [TaskID] = [Forms]![frmWorks]![TaskID

So your Dsum shoud look like this:

DSum("[LineTotal]", "YourTableOrQueryName", _
"[TransactionTypeID] = 2 AND [TaskID] = " & Me.TaskID)

[LineTotal] should be the field in the table or query you want to add up.
If not, change it to the corrent field name.

I'm trying to use DSum to sum the values on a form. The form is called
frmWorksSubform. The field I would like to sum is called LineTotal. The
conditional field is called TransactionTypeID and should = 2.

I have tried setting the control source as:

=DSum("[LineTotal]", "[frmWorksSubform]", "[TransactionTypeID] = 2")

but I just get #error displayed.

If it has to be based on a query or table then I have two conditions,
[TransactionTypeID] = 2 and [TaskID] = [Forms]![frmWorks]![TaskID].

What am I doing wring?

Dave