DSum Syntax Woes

[Bhujanga]

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.

[ADezii]

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:

=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.

[Bhujanga]

The only thing that comes to mind is that if the [ProjectID] Filed was Text instead of Numeric, the syntax would be:

=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!

[ADezii]

You were exactly correct. I must have never used this method before with a text field. Thanks!

Glad it worked for you.