Opening balance required on report

[aamer]

Hi,



I am trying to place an opening balance on between two dates report, I need some guidance how to do that.

[ranman256]

put it in the header.
txtOpenBal = dlookup("[field]","table","[clientID]=" & txtClientID

[John_G]

You haven't give us much information to work with here. Where will that opening balance come from, and how do you determine what it is? What you are going to need is some VBA to get the required value and put it in a field in the report header. What about transactions that occur on the "From" date, e.g. June 28 - are they included in the details or not? Are the "Balance" values included in the source data table or query?

Please provide more information.

[aamer]

please have a look at sample database.

Opening balance should come from 2 sources separately
Purchase Table
Sales Table

There were no transactions on 28 June but there are lot of transactions prior to 28 june, no they are not included in balances.
Those transactions prior to june 28 should be the opening balance.

I have attached a sample db for better understanding

DB OPBal 2015.zip

[aamer]

Have created two new queries

Purchase Totals
Sales Totals

But am unable to get to use them to get opening balance by date.

Need help.

Attached is Sample DB

Op Bal DB.zip

[rpeare]

There's no indication in either the Purchase Totals or Sales Totals queries for which you want to obtain a starting balance. There also isn't an indication of which report you're trying to get this starting balance on. The basic premise of a starting balance/ending balance is that you:

1. Be able to sum all debits and credits PRIOR TO the start of the period you're looking at
2. Sum all debits and credits DURING the period you're looking at

In your case the easiest thing I see to do is, for instance is to set up a 'before' and 'after' column for each variable you want to carry through.

For instance create this query:

SELECT Pchase.PurCoId, Pchase.PurCompanyName, Sum(IIf([purchase date]<[Enter Start Date],(([Purchase Price]*[Purchase Qty]))-[Payment Made],0)) AS PriorPurchaseTotalAmt, Sum(IIf([purchase date] Between [Enter Start Date] And [Enter End Date],(([Purchase Price]*[Purchase Qty]))-[Payment Made],0)) AS PurchaseTotalAmt
FROM Pchase
GROUP BY Pchase.PurCoId, Pchase.PurCompanyName;

Where I have [Enter Start Date] and [Enter End Date] you can substitute fields on a form with

forms![formname]![fieldname] where you would substitute in your actual form name and actual field name.

Then on your report you'd just have to use a dsum or dlookup function to get the starting balance in total from your two queries.

[aamer]

rpeare

I have created and added two queries to the attached DB

Total Purchase
Total Sales

But am unable to place them on the reports

PBcoReport
Sales Recon

Your help is requested.

Opening Balance DB.zip

[rpeare]

Your two queries are using two different forms in their formulas and they are not noted correctly.

Your query TOTAL SALES is using

cboReconDateFrom
cboReconDateTo

These date fields are on the form COSBD so the proper references would be:

[forms]![cosbd]![cboReconDateFrom]
[forms]![cosbd]![cboReconDateTo]

Your date range for the query TOTAL PURCHASE is using

cboDateFrom
cboDateto

Both of these fields are on the form COPBD, there is no reason to have a different 'setup' form for sales or purchases, especially when you want both your sales and purchases to be over the same time span.

If you fix the formulas in one query or the other you can get the starting balance (assuming it was 0 because I don't see a table storing the starting balance) by doing something like:

Create a text box
in the CONTROL SOURCE put the formula (if you are finding the starting balance overall, not just by vendor)

=Dsum("[OpeningSalesBalance]", "Total Sales") - Dsum("[OpeningPurchaseBalance]", "Total Purchase")

if you want opening balances by vendor then you'd use something more like

=Dlookup("[OpeningSalesBalance]", "Total Sales", "[CompanyName] = '" & [Company Field Name] & "'") - Dlookup("[OpeningPurchaseBalance]", "Total Purchase", "[PurCompanyName] = '" & [Company Field Name] & "'")

Where [Company Field Name] (assuming you're using the company name instead of the company PK) is the actual name of the field on your report that contains the company name.