Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-1 Jet 4.0)

    How to Exclude Void Amounts in Payment & Balance Totals?
    a2k (9.0.3821) SR-1 Jet 4.0

    I have a report (rpt_MT_ARF) that list all transactions, some are marked VOID.

    How do I list all transaction and prevent to VOID transaction amounts from adding to the Payment and Balance columns?

    My Balance control source looks like:

    =DSum("[curPAYMENT]","tblSumServe2","[idsKeyOfSumServe2]<=[Reports]![rpt_MT_ARF]![idsKeyOfSum
    Serve2]")

    And this works OK, however contains the VOID amounts.

    I tried the following without success:

    =DSum("[curPAYMENT]","tblSumServe2","[idsKeyOfSumServe2]<=[Reports]![rpt_MT_ARF]![idsKeyOfSumServe2]") And ([Reports]![rpt_MT_ARF]![strVoid]<>"VOID" Or [Reports]![rpt_MT_ARF]![strVoid] Is Null)

    Any clues?

    Thanks, John
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    Try :
    =DSum("[curPAYMENT]";"tblSumServe2";"[idsKeyOfSumServe2]<=[Reports]![rpt_MT_ARF]![idsKeyOfSumServe2] and isnull(strvoid) ")
    Francois

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    Francois's will work if you replace the semi colons with a comma.

    As an alternative, your solution should be changed to:
    =DSum("[curPAYMENT]","tblSumServe2","[idsKeyOfSumServe2]<=[Reports]![rpt_MT_ARF]![idsKeyOfSumServe2] And ([strVoid]<>'VOID' Or [strVoid] Is Null)")

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    Thanks Francois & Pat

    The running total seems to be working with your suggestions

    I have the following questions:

    The Total Payment column is 69,405.70 (payment + voids), which looks to be correct.

    The last Balance running total shows 73,490.70 (payments only)

    Wht isn
    Attached Files Attached Files

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    In your query you have a condition for dteSUMDAY : Between #1/03/2003# And #30/03/2003#
    You have to include the same condition in your DSum function.
    Use :
    =DSum("0+[curPAYMENT]","tblSumServe2","[idsKeyOfSumServe2]<=[Reports]![rpt_MT_ARF]![idsKeyOfSumServe2] and isnull(strvoid) and dtesumday Between #1/03/2003# And #30/03/2003#")

    You have data for the 31/03/2003 that is excluded in the query, so you have to exclude it from the DSum function also.
    Francois

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    The reason is that your report is based on a query with additional conditons imposed (on dteSUMDAY), while the DSum expressions are based on all records of the table. Both DSum expressions should be based on the query too, instead of on the table. But this will make the report excruciatingly slow, because it must execute the query hundreds of times.

    It is much faster to use the RunningSum property of text boxes on a report. Try the following:

    <table border=1><td aling=center>Text Box</td><td aling=center>Control Source</td><td align=center>RunningSum property</td><td>Balance</td><td>=IIf(IsNull([strVoid]),[curPAYMENT],0)</td><td>Over All</td><td>Vodi Balance (sic)</td><td>=IIf(IsNull([strVoid]),0,[curPAYMENT])</td><td>Over All</td></table>
    Note that Sum or DSum is not used in the control source; the RunningSum property takes care of this.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    Thanks Francois & Hans for your helpful response.

    I
    Attached Files Attached Files

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    use:<pre>RunBalance: Format(DSum("curPAYMENT","tblSumServe2","[strCHECKNO]<='" & [strCHECKNO] & "'"),"$00.000,00")</pre>

    Pay attention to the quotes and double quotes
    Francois

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    John,

    Francois has pointed out how to correct the expression.

    But is there a particular reason you want to do this in the query? As I pointed out in my reply to your previous question in this thread, using the RunningSum property of a text box in the report is much more efficient than using this kind of DSum expression.

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    Hi Hans

    I will use your suggestion; I was just trying a few different scenarios for future reference

    What is the best way to list all transactions in a report and exclude the void amounts from the payment column totals?

    Thanks, John
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Exclude Void Amounts in Payment & Balance T (a2k (9.0.3821) SR-

    One way is to have calculated fields CheckAmounts and VoidAmounts in the query (as in qry_MT_ARF in the database you attached), and to put text boxes in the report footer that sum these fields. Only, I wouldn't put the format in the expression, but in the Format property. The Format function is relatively slow:
    CheckAmounts: IIf([strVoid]="VOID",0,[curPayment])
    VoidAmounts: IIf([strVoid]="VOID",[curPayment],0)

    Another way would be to put text boxes in the report footer section that sum the expressions directly:
    =Sum(IIf([strVoid]="VOID",0,[curPayment]))
    =Sum(IIf([strVoid]="VOID",[curPayment],0))
    You wouldn't need CheckAmounts and VoidAmounts in the query then.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •