Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSum with date again (2000/2002)

    Is there a way to add to the below code (a Field in a saved Query) that if the RemitDate is the same as the RemitDate and SOInvoiceNumber is the same the SOInvoiceNumber, that the DSum function will work. And also still work as the code reads.


    PrevPaidCalc:Format(Nz(DSum("RemitAmount","tblCust omerRemitsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDate<#" & Format([RemitDate],"mm/dd/yyyy") & "#"),0),"$#,##0.00")


    I have attached a view of what I
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

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

    Re: DSum with date again (2000/2002)

    I don't understand since I cannot determine where SOInvoiceNumber and RemitDate are in the subform, but does changing < to <= work?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum with date again (2000/2002)

    Oops!

    SOInvoiceNumber is listed as Invoice Number and RemitDate is not visible in the subForm.

    Changing < to<= doesn't work as wanted, this returns a value for those Invoice Numbers that do not have a Previous Payment after the Remit Amt is keyed in and exit that control.

    The brain twist is, If the Invoice Number has a previous payment before this remit date that value is returned, if the Invoice Number does not have a previous payment before this remit date then a zero value is returned. Then the really twisted part, if the Invoice Number does not have a previous payment before this remit date but does have a previous payment for this remit date, I would like this value returned.

    When we receive payment (remittance) checks, one Invoice could have many lines as part of the actual Invoice payment. I hope these bookkeepers can sleep at night.



    Everything I've tried, I can get one part to work but not other.
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

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

    Re: DSum with date again (2000/2002)

    Try this horrendous expression:

    PrevPaidCalc:Format(Nz(DSum("RemitAmount","tblCust omerRemitsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDate<#" & Format([RemitDate],"mm/dd/yyyy") & "#"),Nz(DSum("RemitAmount","tblCustomerRemitsDetai l","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDate=#" & Format([RemitDate],"mm/dd/yyyy") & "#"),0)),"$#,##0.00")

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum with date again (2000/2002)

    Well Hans

    This addition does a circle loop and with a swirl.

    I believe inorder to do this I may have to agh! store the Previous Payment balance. Or this may require a custom function.

    Thank you kindly for your time.

    Tonight I'll sacrifice a virgin steak and potatoes in your honor the gods of Access.
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

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

    Re: DSum with date again (2000/2002)

    English is not my native language, I'm not sure I understand what you're saying here.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum with date again (2000/2002)

    Apologies

    The addition to the expression did not work as planed. The returned value for those Invoices with a previous payment not the same RemitDate, was correct.
    Those Invoices with a previous payment and the same RemitDate returned a Sum of payments, .

    I did try to play with the expression a little. Using the IIf function, IsNull, and Not IsNull. These ideas did not work.

    I did have a thought (just now) maybe adding the RemitDetailID to the expression, that may be what needed to separate the Invoice with the same RemitDates.

    This is curious.
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

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

    Re: DSum with date again (2000/2002)

    Since you were using a DSum, I thought you wanted a sum. If you don't, you'll have to explain what you want instead. Good luck.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum with date again (2000/2002)

    Got it solve

    Should have using the following instead trying to use the RemitDate.

    PrevPaidCalc: Format(Nz(DSum("RemitAmount","tblCustomerRemitsDet ail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitsDetailID<" & [RemitsDetailID]),0),"$#,##0.00")

    Tried to reinvent the wheel,

    Thank you kindly for your time
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

Posting Permissions

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