RE: Access Opening Balance Expression

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date]< Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

.

Relevant Pages
Loading