Results 1 to 3 of 3
  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 (Access 2000,2002)

    The below code is a Field in a saved Query that I am attempting to use to populate a subForm. The subForm and main Form are editable. The first bit of code works fine except that it returns the total sum for the SOInvoiceNumber not those prier to the current RemitDate, I need to filter this a bit, by the previous RemitDate if any and if none exist the return of a 0 (zero). The RemitDate actually is in the tblCustomerRemits table.

    PrevPaid:Format(DSum("RemitAmount","tblCustomerRem itsDetail","SOInvoiceNumber=" & [SOInvoiceNumber]),"$#,##0.00")

    I tried using the following code this returns a sum of all of the records.
    PrevPaid:Format(DSum("RemitAmount","tblCustomerRem itsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] And "RemitDate<" & [RemitDate]),"$#,##0.00")

    The working method of the subForm is, once a SOInvoiceNumber has been selected or entered the other information needed to process a Customers Remittance, most of this is displayed only. What I would like to have happen is that, when at anytime that the record is viewed after it has been entered that it shows the same value as when it was entered, I would rather not store this value.
    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 (Access 2000,2002)

    The syntax of your where-condition is not correct: the And should be within the quotes, and date values should be surrounded by # characters. Also, if there is a chance that you have users with a non-US date format, you must convert the date to mm/dd/yyyy format since that is the only format SQL understands. Try

    PrevPaid:Format(DSum("RemitAmount","tblCustomerRem itsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDate<#" & Format([RemitDate], "mm/dd/yyyy") & "#"),"$#,##0.00")

  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 (Access 2000,2002)

    Thank you kindly, knew I was missing something, just could not see what.
    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
  •