Query - Expression not working

[Moxioron]

Hello.

I think I have a relatively easy ?, but I can't figure it out.

Expr1 in the SQL below works great. However, Expr2 doesn't return anything unless the SumofRedeposit1 column has a value.

Where am I going astray? Thanks for your help.

SELECT tbl_IncomingReturns_FORMATTED.CBAcct, tbl_IncomingReturns_FORMATTED.CustomerName, tbl_IncomingReturns_FORMATTED.CustomerName2, Count(tbl_IncomingReturns_FORMATTED.Amount) AS CountOfAmount, Sum(tbl_IncomingReturns_FORMATTED.Amount) AS SumOfAmount, Sum(IIf(Left([tbl_IncomingReturns_FORMATTED].[Redeposit],2)="**",0,IIf(Left([tbl_IncomingReturns_FORMATTED].[Redeposit],1)="*",1,0))) AS CountOfRedeposit, Sum(IIf(Left([tbl_IncomingReturns_FORMATTED].[Redeposit],2)="*",[Amount])) AS SumOfRedeposit, Sum(IIf(Left([tbl_IncomingReturns_FORMATTED].[Redeposit],2)="**",1,0)) AS CountOfRedeposit1, Sum(IIf(Left([tbl_IncomingReturns_FORMATTED].[Redeposit],2)="**",[Amount])) AS SumOfRedeposit1, [CountOfAmount]-[CountOfRedeposit]-[CountOfRedeposit1] AS Expr1, [SumOfAmount]-[SumOfRedeposit]-[SumOfRedeposit1] AS Expr2
FROM tbl_IncomingReturns_FORMATTED
WHERE (((tbl_IncomingReturns_FORMATTED.Date) Between [Start Date] And [End Date]))
GROUP BY tbl_IncomingReturns_FORMATTED.CBAcct, tbl_IncomingReturns_FORMATTED.CustomerName, tbl_IncomingReturns_FORMATTED.CustomerName2
HAVING (((tbl_IncomingReturns_FORMATTED.CBAcct) Like [Chargeback Acct #] & "*"));

 

[RSXchin]

.

 

[Moxioron]

,

 

[RSXchin]

I was going to suggest changing it to

([SumOfAmount]-[SumOfRedeposit]-[SumOfRedeposit1])

and then I wasn't so sure.


!

 

[Moxioron]

yep. I tried that one. It didn't work. Thanks for trying though.

 

[Moxioron]

Fixed it!

For the Redeposit and Redeposit1 Alias I tacked on a zero at the end of the expression and wah lah.

Sum(IIf(Left([tbl_IncomingReturns_FORMATTED].[Redeposit],2)="**",[Amount],0))