Different totals based on boolean field

[kmclane]

I have a report where in some cases I do not want to include some amounts in my total. Now I have two total fields that are added together [BRTot]+[IntChgAssTot]. I want to only post the value of [BRTot} if the field [Interchange] is true in the table [buyrate]. I thought I could do this with an IIf, but was not successful. Can someone point me in the right direction please.
Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[LarryDeLaruelle]

Ken: An Immediate If should work.

IIf([buyrate].[Interchange] = -1, [BrTot] + [IntChgAssTot], "Your Else condition here")

You will need to include the table BuyRate in the query so you can reference the Interchange field.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[kmclane]

The table is in my query, but if I try to add the field [interchnge], I get the error "No current record". This I do not understand. Can I reference the field even though it is not specifically in the query?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[LarryDeLaruelle]

Ken: The error message is curious if the table is included in the query.

Can you post the SQL for the query (click on View from the menu bar and SQL view to display the code)?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[kmclane]

You Asked for it:

SELECT SortCodes.SortCode, SortCodes.Name1, Sum(EOMVisaMC.MCSalesVol) AS MCSalesVol, Sum(EOMVisaMC.MCNetVol) AS MCNetVol, Sum(EOMVisaMC.VNetVol) AS VNetVol, Sum(EOMVisaMC.VSalesVol) AS VSalesVol, Sum(EOMVisaMC.DebSalesVol) AS DebSalesVol, Sum(EOMVisaMC.DebNetVol) AS DebNetVol, Sum(EOMVisaMC.TotSalesVol) AS TotSalesVol, Sum(EOMVisaMC.TotNetVol) AS TotNetVol, Sum(EOMVisaMC.VMCQualDisc) AS VMCQualDisc, Sum(EOMVisaMC.VMCUnQualDisc) AS VMCUnQualDisc, Sum(EOMVisaMC.VMCAuthInc) AS VMCAuthInc, Sum(EOMVisaMC.CBInc) AS CBInc, Sum(EOMVisaMC.TEAuthInc) AS TEAuthInc, Sum(EOMVisaMC.StateCost) AS SumOfStateCost, Sum(tblBillEOM.CyberCashFee) AS CyberCashFee, buyrate.stmntfee, buyrate.chargebkfee, buyrate.Retfee, buyrate.voice, buyrate.help, buyrate.Monmin, buyrate.authfee, buyrate.tefee, buyrate.debittranfee, buyrate.ResidPct, Sum(EOMVisaMC.DebTotCt) AS DebTotCt, [Vital Support].SumOfActive, VitalSSL.SSLAuth, Sum(tblBillEOM.ActEqWarrFee) AS ActEqWarrFee, Sum(tblBillEOM.DebitTranFee) AS SumOfDebitTranFee, Voicetot.totvoiceauth, RetCnt.SumOfCountOfCard_No, Debacc.DebAccFee, AmexResid.AmexAmnt, Sum(EOMVisaMC.EquipFee) AS EquipFee, Sum(EOMVisaMC.StatementCt) AS StatementCt, Sum(EOMVisaMC.CBCnt) AS CBCnt, Sum(EOMVisaMC.TEAuthCt) AS TEAuthCt, DiscoverResidual.TOTAL, buyrate.IPAuth, buyrate.SSLAuthfee, buyrate.VitalSupport, buyrate.Transaction, buyrate.onfile, buyrate.Newacct, buyrate.CreditRep, buyrate.Deposit, buyrate.pctofqual, buyrate.qualtranrete, buyrate.pctofmidqual, buyrate.midqualrate, buyrate.pctofnonqual, buyrate.nonqualrate, buyrate.mondebit, Sum(tblBillEOM.NewAcct) AS SumOfNewAcct, Sum(tblBillEOMT2.Active) AS SumOfActive1, Sum(tblBillEOMT2.InActive) AS SumOfInActive, Sum(EOMVisaMC.DepFeeCtEDC) AS SumOfDepFeeCtEDC, Sum(tblBillEOM.VQualRetCt) AS SumOfVQualRetCt, Sum(tblBillEOM.VQualSalCt) AS SumOfVQualSalCt, Sum(tblBillEOM.MCQualRetCt) AS SumOfMCQualRetCt, Sum(tblBillEOM.MCQualSalCt) AS SumOfMCQualSalCt, Sum(tblBillEOM.VMidRetCt) AS SumOfVMidRetCt, Sum(tblBillEOM.VMidSalCt) AS SumOfVMidSalCt, Sum(tblBillEOM.VNonRetCt) AS SumOfVNonRetCt, Sum(tblBillEOM.VNonSalCt) AS SumOfVNonSalCt, Sum(tblBillEOM.MCNonRetCt) AS SumOfMCNonRetCt, Sum(tblBillEOM.MCNonSalCt) AS SumOfMCNonSalCt, Sum(tblBillEOM.VQualNetTotAmt) AS SumOfVQualNetTotAmt, Sum(tblBillEOM.MCQualNetTotAmt) AS SumOfMCQualNetTotAmt, Sum(EOMVisaMC.MCAuthCt) AS SumOfMCAuthCt, Sum(EOMVisaMC.VAuthCt) AS SumOfVAuthCt, Sum(tblBillEOM.PlateCount) AS SumOfPlateCount, Sum(EOMVisaMC.MinMthFee) AS SumOfMinMthFee, MinMonthFee.CountOfMinMthFee, Sum(EOMVisaMC.MCQualIntchg) AS SumOfMCQualIntchg, Sum(EOMVisaMC.MCUnQualIntchg) AS SumOfMCUnQualIntchg, Sum(EOMVisaMC.MCDuesAss) AS SumOfMCDuesAss, Sum(EOMVisaMC.VQualIntchg) AS SumOfVQualIntchg, Sum(EOMVisaMC.VUnQualIntchg) AS SumOfVUnQualIntchg, Sum(EOMVisaMC.VDuesAss) AS SumOfVDuesAss, AuthCount.SumOfNobelTotal_Auths, AuthCount.SumOfTotal_Auths, AuthCount.SumOfVitalTotal_Auths, DebMrchCt.CountOfDebMrchCt, buyrate.ImpPltFee, Sum(tblBillEOM.VMidNetTotAmt) AS SumOfVMidNetTotAmt, Sum(tblBillEOM.MCMidNetTotAmt) AS SumOfMCMidNetTotAmt, Sum(tblBillEOM.VNonNetTotAmt) AS SumOfVNonNetTotAmt, Sum(tblBillEOM.MCNonNetTotAmt) AS SumOfMCNonNetTotAmt, Sum(tblBillEOM.MCNonRetCt) AS SumOfMCNonRetCt1, Sum(tblBillEOM.MCNonSalCt) AS SumOfMCNonSalCt1, Sum(tblBillEOM.MCMidRetCt) AS SumOfMCMidRetCt, Sum(tblBillEOM.MCMidSalCt) AS SumOfMCMidSalCt
FROM DebMrchCt RIGHT JOIN (AuthCount RIGHT JOIN (([Vital Support] RIGHT JOIN (Debacc RIGHT JOIN (((DiscoverResidual RIGHT JOIN (Voicetot RIGHT JOIN (buyrate RIGHT JOIN (AmexResid RIGHT JOIN (RetCnt RIGHT JOIN (VitalSSL RIGHT JOIN ((EOMVisaMC LEFT JOIN SortCodes ON EOMVisaMC.Sort = SortCodes.SortCode) LEFT JOIN tblBillEOM ON EOMVisaMC.MID = tblBillEOM.strMID) ON VitalSSL.Name1 = SortCodes.Name1) ON RetCnt.Name1 = SortCodes.Name1) ON AmexResid.Name1 = SortCodes.Name1) ON buyrate.Agentcode = SortCodes.Name1) ON Voicetot.Name1 = SortCodes.Name1) ON DiscoverResidual.NAME1 = SortCodes.Name1) LEFT JOIN tblBillEOMT2 ON EOMVisaMC.MID = tblBillEOMT2.MID) LEFT JOIN Query5 ON SortCodes.SortCode = Query5.SortCode) ON Debacc.SortCode = EOMVisaMC.Sort) ON [Vital Support].SortCode = EOMVisaMC.Sort) LEFT JOIN MinMonthFee ON SortCodes.SortCode = MinMonthFee.SortCode) ON AuthCount.Sort = EOMVisaMC.Sort) ON DebMrchCt.SortCode = EOMVisaMC.Sort
GROUP BY SortCodes.SortCode, SortCodes.Name1, buyrate.stmntfee, buyrate.chargebkfee, buyrate.Retfee, buyrate.voice, buyrate.help, buyrate.Monmin, buyrate.authfee, buyrate.tefee, buyrate.debittranfee, buyrate.ResidPct, [Vital Support].SumOfActive, VitalSSL.SSLAuth, Voicetot.totvoiceauth, RetCnt.SumOfCountOfCard_No, Debacc.DebAccFee, AmexResid.AmexAmnt, DiscoverResidual.TOTAL, buyrate.IPAuth, buyrate.SSLAuthfee, buyrate.VitalSupport, buyrate.Transaction, buyrate.onfile, buyrate.Newacct, buyrate.CreditRep, buyrate.Deposit, buyrate.pctofqual, buyrate.qualtranrete, buyrate.pctofmidqual, buyrate.midqualrate, buyrate.pctofnonqual, buyrate.nonqualrate, buyrate.mondebit, MinMonthFee.CountOfMinMthFee, AuthCount.SumOfNobelTotal_Auths, AuthCount.SumOfTotal_Auths, AuthCount.SumOfVitalTotal_Auths, DebMrchCt.CountOfDebMrchCt, buyrate.ImpPltFee
ORDER BY SortCodes.Name1;

This is for page 2 of my report
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

I found the problem, I had to make sure there was a matching entry for every record, then the query runs and the code works fine. Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[LarryDeLaruelle]

Ken:

That's a monster.

I wasn't sure an IIf would work in a Totals query so I ran a quick test using your format -- if a check box was true, display only the one value, else add the two values.

My test data was only half a dozen records with three groups but the IIf worked as advertised. Is your statement in this format:

Alias: IIf([buyrate].[Interchange] = -1, [BrTot], [BrTot] + [IntChgAssTot])

I think I had the logic reversed in my first post, sorry.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[kmclane]

Yep that's the way I have it, see my previous post for the reason for the error. Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[LarryDeLaruelle]

Ken:

Not sure we're getting any closer but we'll keep plugging.

Does the query run correctly without the IIf statement?

Have you tried a query using just the tables necessary with that IIf? Both as a simple select and then as a Select/Total?

Let me know.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[kmclane]

Sorry, you missed it. It's working after I fixed the problem with not having an entry for each record. Boolean fields cannot be null, and since I didn't have a record in the buyrate table, it was barfing with "no current record". Any other type of field would have just posted a null entry. Once I made sure there was an entry for each Agent in the buyrate table, my query ran fine, and the iif statement worked as expected. All this for three agents out of 130 that are "different". Now onto whole other sections to be added for 5 OTHER agents that are different, and need to make that section disappear for everyone else. I think I can do this by using subreports and making them visible or not with my boolean fields. Thanks for your help.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[LarryDeLaruelle]

Happy you figured it out.

It would be nice if Access errror messages were a bit more explanatory.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org