Monthly Billed and Payment Received Report

[BrittKnee]

Hi! I am building a report which currently allows the user to run the results based on a date range (they choose this on the main form to run the report). This works fine as far as total billed goes as it runs off of the Invoice Date, however I need the payment totals based off of payment dates. Right now, the report returns payments that are in the date range for total billed. Below is an example:



Invoice # Invoice Date Billed Payment Date Payment Amount
1 2/1/20 $19 3/1/20 $15
2 2/1/20 $10 4/14/20 $10
3 3/1/20 $10 5/10/20 $5
4 3/1/20 $15 5/28/20 $12


The Report Totals the number of Invoices By Month and Amount Billed By Month, but the Payment Amounts are showing in the month billed. So in February it would have 2 Invoices, $29 Billed, and $10 even though this was paid in April. The Payment Amount for February should be $0. Your help is appreciated!!

[June7]

Why are payments showing associated with Billed that way? Is this your raw data or a query? If you want to provide db for analysis, follow instructions at bottom of my post.