Sum of Records against a boolean

[EriccW]

I need to sum the total of records which are yes or no.

Fields
[AmountToBeCollected] is currency
[Recoverable] is a boolean

I want to sum [AmountToBeCollected] where [Recoverable] is 0.

Currently I'm using an unbound text box, but I'm not sure if I should set it
up as a part of my query.

Ericc

 

[Douglas J. Steele]

Try setting the ControlSource of the text box to

=DSum("AmountToBeCollected", "NameOfTable", "Recoverable = 0")

 

[mray29]

If you're going to query a table, you'll need to have the Recoverable field
available in the table so you can use a where clause on it. I would include
it in the table and bind the form text box to it.

The SQL for the sum would be something like
SELECT Sum(Table2.Qty) AS SumOfQty
FROM Table2
WHERE (((Table2.Recoverable <> 0)));

 

[EriccW]

Unfortunately, this didn't work. BUT it did get me on the right track. Here's
the code which worked:
=DSum("[AmountToBeCollected]", "[NameOfTable]", "[Recoverable] = 0")

I just needed brackets. Thanks so much.

Ericc

Try setting the ControlSource of the text box to

=DSum("AmountToBeCollected", "NameOfTable", "Recoverable = 0")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to sum the total of records which are yes or no.

Fields
[AmountToBeCollected] is currency
[Recoverable] is a boolean

I want to sum [AmountToBeCollected] where [Recoverable] is 0.

Currently I'm using an unbound text box, but I'm not sure if I should set
it
up as a part of my query.

Ericc

 

[Douglas J. Steele]

The only reason the square brackets should be required is if you've got
spaces in the names.

I should, of course, have included them in my response, though, in that
event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Unfortunately, this didn't work. BUT it did get me on the right track.
Here's
the code which worked:
=DSum("[AmountToBeCollected]", "[NameOfTable]", "[Recoverable] = 0")

I just needed brackets. Thanks so much.

Ericc

Try setting the ControlSource of the text box to

=DSum("AmountToBeCollected", "NameOfTable", "Recoverable = 0")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to sum the total of records which are yes or no.

Fields
[AmountToBeCollected] is currency
[Recoverable] is a boolean

I want to sum [AmountToBeCollected] where [Recoverable] is 0.

Currently I'm using an unbound text box, but I'm not sure if I should
set
it
up as a part of my query.

Ericc