Outer Join Issues - Please Help

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.

Select *
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.

Thank you all!Actually, I think I'm on the right path here using a derived table, please correct me if I'm wrong:

SELECT *
FROM (SELECT * FROM
AROPNFIL WHERE Doc_date BETWEEN @.StartDate AND @.EndDate) RIGHT OUTER JOIN
fnBalance(@.StartDate) StartBalance ON AROPNFIL.cus_no = StartBalance.cus_no

Obviously, I'll get rid of the *s, but no one here cares about what columns I'm actually pulling out.|||This is correct, I got it to work!!!