By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,107 Members | 1,245 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,107 IT Pros & Developers. It's quick & easy.

Dsum Function. What is wrong with my expression?

P: 5
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 :))
Nov 13 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
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.

Nov 13 '08 #2

P: 5
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 :))
Nov 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.