put it in the header.
txtOpenBal = dlookup("[field]","table","[clientID]=" & txtClientID
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.
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
Last edited by aamer; 07-18-2015 at 12:16 AM.
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
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:
Where I have [Enter Start Date] and [Enter End Date] you can substitute fields on a form withCode: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;
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.
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
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.