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

DSum Syntax Woes

P: 44
I have a field on a form that I'm trying to show the total of the invoices that have come in for the project. The ProjectID is field on the form which is in the forms primary table (ContractInfo). There is a child table called "Invoices" which is also has a ProjectID field that ties those records to the ContractInfo table, and of course it has a field for the InvoiceAmt. So in this ContractInfo form I have a text box and I'm using the following syntax to try and show the invoice total for the contract. So I use this statement, which I swear has worked for me many times before, but it doesn't work now - the text box shows "#error".

=DSum("[InvoiceAmt]","Invoices","[ProjectID] = " & [ProjectID])

If I knock off the where clause it works, but of course it's grabbing all of the invoices in the table - but it shows that it's the where clause that the trouble lies. I've checked and triple checked that the control on the form is called "ProjectID" and that the field in the "Invoices" table is called "ProjectID", so what doesn't it like? Below is the syntax right from the Microsoft Help site.

DSum("[Quantity]*[UnitPrice]", "Order Details", "[ProductID] = " & [ProductID])

Does anyone know what might be wrong? If not, is there a better way to accomplish this?
Thanks.
Feb 5 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
I have a field on a form that I'm trying to show the total of the invoices that have come in for the project. The ProjectID is field on the form which is in the forms primary table (ContractInfo). There is a child table called "Invoices" which is also has a ProjectID field that ties those records to the ContractInfo table, and of course it has a field for the InvoiceAmt. So in this ContractInfo form I have a text box and I'm using the following syntax to try and show the invoice total for the contract. So I use this statement, which I swear has worked for me many times before, but it doesn't work now - the text box shows "#error".

=DSum("[InvoiceAmt]","Invoices","[ProjectID] = " & [ProjectID])

If I knock off the where clause it works, but of course it's grabbing all of the invoices in the table - but it shows that it's the where clause that the trouble lies. I've checked and triple checked that the control on the form is called "ProjectID" and that the field in the "Invoices" table is called "ProjectID", so what doesn't it like? Below is the syntax right from the Microsoft Help site.

DSum("[Quantity]*[UnitPrice]", "Order Details", "[ProductID] = " & [ProductID])

Does anyone know what might be wrong? If not, is there a better way to accomplish this?
Thanks.
The only thing that comes to mind is that if the [ProjectID] Filed was Text instead of Numeric, the syntax would be:
Expand|Select|Wrap|Line Numbers
  1. =DSum("[InvoiceAmt]","Invoices","[ProjectID] = '" & [ProjectID] & "'")
Other than that I can't think of anything else since you already verified other info that could concievably cause the problem. If you like, you can send me the DB as an E-Mail Attachment and I would be happy to have a look at it.
Feb 6 '08 #2

P: 44
The only thing that comes to mind is that if the [ProjectID] Filed was Text instead of Numeric, the syntax would be:
Expand|Select|Wrap|Line Numbers
  1. =DSum("[InvoiceAmt]","Invoices","[ProjectID] = '" & [ProjectID] & "'")
Other than that I can't think of anything else since you already verified other info that could concievably cause the problem. If you like, you can send me the DB as an E-Mail Attachment and I would be happy to have a look at it.
You were exactly correct. I must have never used this method before with a text field. Thanks!
Feb 6 '08 #3

ADezii
Expert 5K+
P: 8,597
You were exactly correct. I must have never used this method before with a text field. Thanks!
Glad it worked for you.
Feb 6 '08 #4

Post your reply

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