I am working on the record source for a report to produce monthly customer statements. Thanks to everyone here, I have been able to overcome many, many hurdles I have encountered. I just have one last issue I need to get resolved (famous last words, I know).
At the heart of my record source are two entities:
1. AROPNFIL - A table of all Accounts Receivable items (invoices, payments, credits, etc.)
2. fnBalance(@StartDate) - A table valued function that gives me the starting balance for a customer on a particular date by summing the net Amounts of all items in the AROPNFIL that occured before the @startdate. (i.e. Invoices have a positive amount, payments have a negative amount, so they net out so that only unpaid invoices remain.)
These are joined on customer_number. The query also selects only the rows in the AROPNFIL table that are between @StartDate and @EndDate for the month.
Everything was going great until...I realized that no statement was being created for a customer if they didn't have any activity in the current month, even though they had a starting balance.
So I tried an outer join, telling the query to select all rows from the fnBalance table. But that still wasn't returning the rows I wanted. After several hours of cursing and feeling the need for a drink, I realized why that wasn't working. Because the customer in question, let's call it "Coast01" had rows in the AROPNFIL table before the @StartDate, it was seeing that as having completed the join and hence no reason to return a row for that starting balance.
So, this is what I need, a way to write what I am going to try to interpret as the following.
From (AROPNFIL WHERE Doc_date BETWEEN @StartDate AND @EndDate) RIGHT OUTER JOIN fnBalance(@StartDate)
Does that make sense? I need the join with fnBalance to take place after the query has selected only the rows between @startdate and @enddate so that the row for the balance for "Coast01" will appear even though there is no activity in the current month.
But I am at a loss for how to write that in the FROM and WHERE clauses. I guess one way would be to create a query selecting rows from AROPNFIL WHERE doc_date BETWEEN @StartDate AND @EndDate and then create another query performing the outer join between that query and fnBalance, but I don't really want to do that because the rest of my record source query is actually a lot more complicated than what I've explained here, and I'd rather not have to create additional queries if I don't have to. But if you guys tell me there is no other way, I'll believe you.