By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

DSUM with fields from different tables?

P: 1
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]")

*note: [Received] is referring to a column in the same query the calculated field is in.

But, what comes up is a prompt for "KYOWAPayment". I enter "[KYOWAPayment]" then click OK, 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!
Apr 13 '12 #1
Share this Question
Share on Google+
2 Replies

P: 10
Hmm, i think i had this or somthing like in access before. i might be wrong but i tend to cobble things to get them to work and i think i ended up creating a textbox and seting it to do a dlookup for the required value, then i think access could not work with the value from the dlookup although it did display the answer so i think i then added another text box and set its value to be that of the previous then used the new text box value in the calc. then i hid the two boxes to keep it clean. it was a while ago so i might be wrong.
Apr 20 '12 #2

Expert Mod 15k+
P: 31,418
I'm not sure where Received comes from in your code but I would guess it is a control on a form. In that case you would probably be looking at something like :
Expand|Select|Wrap|Line Numbers
  1. Paid: DSum("Amount","[KYOWAPayment]","[Wire Date]<=" & Format(Forms!YourFormName!Received,"\#m\/d\/yyyy\#"))
Apr 21 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.