Hello. I am have created a database in Access 2003 that will be used to track receipts and disbursements. I am having some trouble with Beginning and Ending balances in the report. I attached a ZIP file that contains the databse I created named New_Transactions.mdb. Basically, the report that I am having trouble with is rptMonthlyFundSummary. This report is based on a query which pulls the Date, Fund Number, Fund Name, Receipt Amount, and Disbursement amount from tblAllTransactions for each fund (and there are multiple funds). The query has a parameter for the date so the user can enter a begin and end date range. The report is grouped by fund number and fund name and then shows the beginning balance, sum of receipts, sum of disbursements and then an ending balance for each fund. To get the initial beginning balance, each fund has a receipt entry on 12/31/2006 and this is the beginning balance for January. The beginning balance comes from the text box named BegBalance with the following calculation in the control source:
=DSum("[Receipt Amount]","tblAllTransactions","[Date] < #" & [Enter Begin Date] & "# AND [FUND Number] ='" & [FUND Number] & "'")-DSum("[Disbursement Amount]","tblAllTransactions","[Date] < #" & [Enter Begin Date] & "# AND [FUND Number] ='" & [FUND Number] & "'")
The text box EndBalance calculates the ending balance as follows:
=[BegBalance]+[SumReceipt]-[SumDisbursement]
The report displays the beginning and ending balances only if the fund had receipts and disbursements for a given month. For example if you use 1/1/07 through 1/31/07 for the date range...you will notice that fund 130 does not appear in the report. Fund 130 has a receipt entry on 12/31/06 in the amount of $9,277,694.95, but it does not have any receipts or disbursements for January. I would like to know if there is a way to get this to still display on the report. I would like it to still list the fund with a beginning balance of $9,277,694.95, $0.00 for receipts, $0.00 for disbursements and an ending balance of $9,277,694.95. This is also an issue if a fund did have transactions in January, but then did not have transactions for February. In this case, I still want to see the fund's January ending balance as the fund's February beginning balance and then have $0.00 for receipts and disbursements on the report. If you were to run the report now with a date range of 2/1/07 through 2/28/07, the fields all display as #ERROR. I would like to see it display all the fund's ending balances from January as the beginning balance for February. Can this be accomplished? I have built many basic Access database, but have not done much with calculations in reports...so any help on this would be greatly appreciated. Please let me know if I need to clarify anything. I have also attached my database.
Thank You,
Shannon
=DSum("[Receipt Amount]","tblAllTransactions","[Date] < #" & [Enter Begin Date] & "# AND [FUND Number] ='" & [FUND Number] & "'")-DSum("[Disbursement Amount]","tblAllTransactions","[Date] < #" & [Enter Begin Date] & "# AND [FUND Number] ='" & [FUND Number] & "'")
The text box EndBalance calculates the ending balance as follows:
=[BegBalance]+[SumReceipt]-[SumDisbursement]
The report displays the beginning and ending balances only if the fund had receipts and disbursements for a given month. For example if you use 1/1/07 through 1/31/07 for the date range...you will notice that fund 130 does not appear in the report. Fund 130 has a receipt entry on 12/31/06 in the amount of $9,277,694.95, but it does not have any receipts or disbursements for January. I would like to know if there is a way to get this to still display on the report. I would like it to still list the fund with a beginning balance of $9,277,694.95, $0.00 for receipts, $0.00 for disbursements and an ending balance of $9,277,694.95. This is also an issue if a fund did have transactions in January, but then did not have transactions for February. In this case, I still want to see the fund's January ending balance as the fund's February beginning balance and then have $0.00 for receipts and disbursements on the report. If you were to run the report now with a date range of 2/1/07 through 2/28/07, the fields all display as #ERROR. I would like to see it display all the fund's ending balances from January as the beginning balance for February. Can this be accomplished? I have built many basic Access database, but have not done much with calculations in reports...so any help on this would be greatly appreciated. Please let me know if I need to clarify anything. I have also attached my database.
Thank You,
Shannon
