Sum of Records against a boolean

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try setting the ControlSource of the text box to

=DSum("AmountToBeCollected", "NameOfTable", "Recoverable = 0")
 
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)));
 
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
Douglas J. Steele said:
Try setting the ControlSource of the text box to

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


EriccW said:
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
Click to expand...
Click to expand...
 
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!)


EriccW said:
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
Douglas J. Steele said:
Try setting the ControlSource of the text box to

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


EriccW said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top