Dsum Function. What is wrong with my expression?

[Gulnarambi]

I have a form in datasheet view with Total and Currency text boxes, in Currency text box I only can enter values of AZN and USD.
Form is based on Table and as criterias information fromUnbound control from another form is used,
In form footer I need to create too text boxes, one of then shoul calculate sum of Total for records where currency field equals to USD and other one calculates sum for Total for records where Currency field equals to AZN. I tried to use SQL in data source property for those fields. I faild, then try to calculate using DSUM function.

Here is my expression. What is wrong? in form view i see #Error as a result.

=DSum("[frmInvoicesResult]![Total]","frmInvoicesResult","[frmInvoicesResult]![Currency] = 'USD'")

Thanks in advance

Ambi :))

[Stewart Ross]

Hi. Sorry, but you can't achieve this using DSum the way you are trying to do. DSum sums fields in the specified table or query. It does not sum unbound textboxes - that is not its purpose. The help file data available off-line and on-line as part of Office Pro will guide you how to use DSum.

Your table/query reference is also in error - you are supplying the name of your form in place of a valid table or query.

And finally you are also supplying a similarly incorrect form control reference in the Where clause.

The #Error you are seeing results from Access not being able to recognise any of the things you tell it to do - the field to be summed, the table you want to sum within, and the Where clause are all incorrect.

As you have criteria involved you will either need to use DSum correctly, specifying the relevant field from the underlying table you want to sum and supplying a suitable where clause to restrict your results, or you will need to reconsider entirely the values you are including in your underlying query so that the sum of the relevant fields can be done in the report itself.

-Stewart

[Gulnarambi]

Thank You Very Much, Stewart!

So as I understood the problem is that the form is not the date source, but just ... form))


Because this one works properly))

=DSum("[QryPriceStatistics]![Price]","QryPriceStatistics","[QryPriceStatistics]![Status] = 'Recommended'")

Thanks again, will try another way.

Ambi :))