I have a Members table, Billing table, and Payment table. I wrote an expression for a from that looks like this:

=DSum("[Amount]","Payment","[Date] > #30/11/2001# And Payment!Member_ID=[MemberID]")

It works fine, filtering the payments for dates more recent than 30/11/2001.

I tryed to move this to a query that included all three tables and it doesn't work. It tells me that it cann't find the field MemberID. I tryed to change it to Members.MemberID, Members!MemberID, [Members]![MemberID], and [Members].[MemberID]

Nothing seems to work.

Why use DSum in a query?

I created a form that lists the Annual Dues and any Payments that are within the last billing cycle:

=DSum("[Amount]","Payment","[Date] > #30/11/2001# And Payment!Member_ID=[MemberID]")

Now I am trying to calculate the difference;

[Annual Dues] - [the above expresion]

I keep getting a #name? in that field. I thought I would try and do the calculations in a query but I am having problems.