Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access: DSUM with criteria that compares fields from two different tables?

    I am trying to add a calculated field in my query to sum the payments up until the date of the entry. There are two tables involved. I have a table for the dates and amounts of wired payments ("KYOWAPayments") as well as a table of invoice# and shipping information ("AP statement log"). The two tables have no relationship.

    I wanted to make a field that will automatically calculate the sum of all wired payments up until the date of each entry, so I tried using DSUM:

    Paid: DSum("Amount",[KYOWAPayment],"[KYOWAPayment]![Wire Date]<= [Received]")

    But, what comes up is a prompt for "KYOWAPayment". I don't really understand why a prompt suddenly comes up, but I enter "[KYOWAPayment]" then click OK just to see what happens, and another dialog box comes up with following message: "The expression you entered as a query parameter produced this error: 'Microsoft Access cannot find the name 'Received' you entered in the expression'."

    This is the first time I am dealing with databases at all, and I've tried everything I can think of. I tried making a spreadsheet form with a calculated text box field as well, but a #NAME? error came up. I also tried making a split form and putting the KYOWAPayment table in the top section and tried reproducing the query in the bottom, spreadsheet section by adding a calculated text box field to no avail. Is there anything I could do? I have a large amount of data, and I'd hate to have to go back and edit an excel file over and over again every time I need to update. I'd be extremely grateful for any help!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Paid: DSum("Amount",[KYOWAPayment],"[KYOWAPayment]![Wire Date]<= [Received]")
    The Syntax for DSUM is:DSum(Expr, Domain, Criteria)

    Paid: DSum("Amount","KYOWAPayment","[Wire Date]<=#" & [Received] & "#")

    But what is Received? Is that a field in the table KYOWAPayment ? Presumably WireDate is a field in KYOWAPayment.
    You said that two tables are involved? Does the second table come into the DSUM at all?
    Regards
    John



Tags for this Thread

Posting Permissions

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