Calculation if Yes/No field = Yes 1

[dr00bie]

I have a report that I am building and have gotten to another obstacle...

I need to calculate a column total, but only if the column is YES.

Anyone have any advice?

Thanks,
Drew Laing

 

[dhookom]

If you want to count the number of Yes values in a Yes/No field in a group or report footer, add a text box with a control source property of:
=Sum( Abs([YesNoFieldName]) )


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dr00bie]

Not really looking for that.

I have the following columns,

PONum - number
PODate - date
Description - text
Amount - currency
Complete - yes/no
Carryover - yes/no

The PO is either Completed or a carryover. I need to show a sum of all the Amounts where Carryover = Yes.

Thanks,
Drew

 

[dhookom]

=Sum( Abs([Complete]) * [Amount] )


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dr00bie]

Ok... I see what your code is trying to do, but I have failed to mention that this is actually in an ADP file (didn't think it would matter too much). Now I am getting the following error,

Aggregate functions are only allowed on output fields of the Record Source

I have searched around and found that I need to do one of the calculations in a seperate textbox, then Sum the textbox. Problem is that this calculation is in the Report Header, so the textbox isn't there when I try to sum it.

Any advice?

Thanks,
Drew

P.S. Duane, I want to thank you so very much for your dedication to helping people. I have been searching around for a while and keep seeing your name pop up. You are a great asset to this community!

 

[dhookom]

Does your report's record source NOT include the Completed and Amount fields? You could try create a new field in the query/view
AmountSold: Abs([Complete]) * [Amount]
Then sum(AmountSold)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dr00bie]

I am using a Stored Proc off the SQL Server for the report. I just tried adding another query to the SP that does this calculation. In Query Analyzer, it returns the data as a seperate query, but in Access I cannot use that field.

Any ideas?

Thanks,
Drew

 

[dhookom]

Without seeing your stored procedure or other query or tables or record source or report, it is a bit difficult to trouble-shoot your problem. This shouldn't be that difficult.

Transact SQL could derive a column like:

Case When Complete <> 0 Then Amount Else 0 As AmountSold End

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dr00bie]

Here is my SP

CREATE PROCEDURE spCreditCardLog
@UserName varchar(30)
AS
--Get all records by username that is logged in
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber, CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode, Complete, Carryover
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID = CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE C.CardholderID = @UserName
ORDER BY C.PONum;
SELECT Sum(Amount) AS CarryoverTot
FROM CreditCardLog
WHERE Carryover = 1
GO

Thanks,
Drew

 

[dhookom]

Did you try insert my suggested expression?
CREATE PROCEDURE spCreditCardLog
@UserName varchar(30)
AS
--Get all records by username that is logged in
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber, CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode, Complete, Carryover,
Case When Complete <> 0 Then Amount Else 0 As AmountSold End as CompleteAmount
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID = CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE C.CardholderID = @UserName
ORDER BY C.PONum;
SELECT Sum(Amount) AS CarryoverTot
FROM CreditCardLog
WHERE Carryover = 1


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dr00bie]

Getting the following error,

"Incorrect syntax near the keyword 'As'."

Thanks,
Drew

 

[dhookom]

I had to AS
CREATE PROCEDURE spCreditCardLog
@UserName varchar(30)
AS
--Get all records by username that is logged in
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber, CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode, Complete, Carryover,
Case When Complete <> 0 Then Amount Else 0 End as CompleteAmount
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID = CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE C.CardholderID = @UserName
ORDER BY C.PONum;
SELECT Sum(Amount) AS CarryoverTot
FROM CreditCardLog
WHERE Carryover = 1


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dr00bie]

BEAUTIFUL!

Thanks a bunch for your help!

Drew