DSUM with fields from different tables?

[asmodea]

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 "KYOWAPayme nt". 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!

[nickstock]

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.

[NeoPa]

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 :

Paid: DSum("Amount","[KYOWAPayment]","[Wire Date]<=" & Format(Forms!YourFormName!Received,"\#m\/d\/yyyy\#"))