Access Opening Balance Expression

[Difficult1]

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!

[Duane Hookom]

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]

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]

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.

[Difficult1]

Hi, Duane. Thank you for your response. I did set up a form that asks for
starting and ending dates, but, really wasn't sure where to go with it from
there. It had 3 objects on it... Start Date, End Date and a Preview button.
Is that the form I would put in place of frmDateSelect? Maybe I was halfway
on the right track! I will give this a try and see where it gets me.

Thank you so much!

[Duane Hookom]

If you already have a form to enter dates, you don't need to create a new one.

[Duane Hookom]

A sample of how this can work with a union query, you can create a query in
the Northwind sample mdb to sum Freight prior to a date as a beginning
balance:

SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, Freight
FROM Orders
WHERE OrderDate Between [Forms]![frmDateSelect]![txtStart] And
[Forms]![frmDateSelect]![txtEnd]
UNION
SELECT 0, CustomerID,Null, Null, Null, Sum(Freight)
FROM Orders
WHERE OrderDate < [Forms]![frmDateSelect]![txtStart]
GROUP BY CustomerID
ORDER BY 2,1;

[Difficult1]

Excellent. Let me give this a try and see how far I get. I set up the form
and then abandoned that idea because I wasn't sure how to bring it in. I
think your example gives me just what I need to make this work.

Thank you!

[Difficult1]

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]

"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.

[Difficult1]

Ha! Yeah. I just threw Query2 there because I couldn't remember exactly what
I called it. :-) Thanks a million! You are awesome.

I wish I knew 1/8th of what you guys know when it comes to this stuff. I've
learned to appreciate Access and its capabilities, but, have struggled with
getting comfortable at learning some of the more challening bits. Excel has
always been my software of choice, which is how I used to track the database
I am now creating.

Have a great weekend!

[Difficult1]

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]

Did you try the union query solution I suggested a while back?

[Difficult1]

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

[Duane Hookom]

You might want to share your union query SQL view with us and describe the
results.

[Difficult1]

SELECT ChildID,
FROM Transactions
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT 0, CustomerID,Null, Null, Null, Sum(BegBal)
FROM Transactions
WHERE Date < [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;

I get a message that says the number of columns do not match. I have never
done one of these before, so I feel kinda stupid.

[Duane Hookom]

As per the error message, the number of columns/fields in each of the SELECT
clauses in a union query must be equal. Your first SELECT has only one column
with a orphaned comma while your second SELECT has 6 columns.

Your first SELECT should have all of the fields needed in your report. The
second select should be the GROUP BY query which will probably have Null for
most of the columns. Do you actually have a BegBal field in the Transactions
table? I would expect the second SELECT would SUM a transaction amount.

[Difficult1]

Okay, so, with some modifications, I am getting all of the information that I
need into my report. The only issue I am running into now is the dollar
amount in the BegBal calculation. I want it is sum everything before the
FromDate. It looks like it is using the same number for each individual
($84), and not adding them up per ChildID before FromDate.

SELECT ChildID, ChildFirst, ChildLast, ParentFirst, ParentLast, BegBal,
Amount, PaymentAmt, StmtAmt, Description, Method, Date, Address1, City,
State, Zip, Hours, TimeIn, TimeOut, CheckNumber
FROM qTransbydate

WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]

UNION SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM qTransbyDate

WHERE Date < [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;

[Duane Hookom]

Good work. What do you see if you create a query with only this part of the
UNION query:

SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM qTransbyDate
WHERE [Date] < [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;

--

[Difficult1]

Still the same thing. Maybe I am putting the [BegBal] field in the wrong
place? I have a ChildID header with all the names and addresses, and that is
where I have it. Then in the ChildID detail section, I have all of my detail
for that month. It appears that the number coming into BegBal is the first
charge listed in the detail.

for example:

ChildID Header
BegBal = $84.00

ChildID Detail
7/1/09 Daycare $84.00

[Difficult1]

Okay, so, in looking at this some more... I have found a couple of other
things...

First, the beginning balance of the first ChildID in the query itself is
correct -50.30. It isn't pulling that same number into the report, it is
pulling $84. The second ChildID doesn't have any records between those dates,
so it isn't bringing the header info in (Name, Address, so forth), but, it is
bringing in the correct beginning balance (nothing else on the page). The
third set of records that have test data should have a BegBal of -0- but it
is showing $84 and the first line of detail is $84.

The query itself is showing me correct beggining balances and only the
details I requested, with the exception of the name and address info for the
one with -0- beggining balance.

Now I'm really confused, but, I think I am sooooo very close to having this
right.

[Duane Hookom]

Why doesn't your union query select the Sum(StmtAmt) rather than some BegBal
field? I don't know where BegBal came from.

[Difficult1]

The StmtAmt field is a formula field that combines charges and payments into
one column. I have it set up that way because I use a drop down field that
automatically fills a field with $84 or the number of hours x $4.20, so, it
won't let me type a payment in there and override it.

Trust me, I am just as confused by it. I think I am so close though.

[Duane Hookom]

I think the key is where BegBal comes from. What is the SQL view of
qTransbyDate? How is StmtAmt calculated?

[Steve]

Hello!

You certrainly have spent a lot of time and frustration on your problem and
still don't have a solution. The chances of you getting a solution appears
to be very dim. I would be glad to take a look at your problem and try and
come up with a solution for a modest fee. I provide help with Access, Excel
and Word applicayions for a small fee. If you want my help, contact me.

Steve
san...@penn.com

"Difficult1" <Diffi...@discussions.microsoft.com> wrote in message
news:6975DC49-B6A2-4220...@microsoft.com...

[StopThisAdvertising]

"Steve" <notmy...@address.com> schreef in bericht
news:lKCdnfVs3JGuLDPX...@earthlink.com...

> Hello!
>
> You certrainly have spent a lot of time and frustration on your problem and still don't
> have a solution. The chances of you getting a solution appears to be very dim. I would
> be glad to take a look at your problem and try and come up with a solution for a modest
> fee. I provide help with Access, Excel and Word applicayions for a small fee. If you
> want my help, contact me.

--
Get lost $teve, This newsgroup is meant for FREE help..
You do want me to 'mark' *all* your posts??? OK, please go ahead with your advertising
here...

No-one wants you here... no-one needs you here...
OP look at http://home.tiscali.nl/arracom/whoissteve.html

Arno R

[John... Visio MVP]

"Steve" <notmy...@address.com> wrote in message
news:lKCdnfVs3JGuLDPX...@earthlink.com...

> Hello!
>
> You certrainly have spent a lot of time and frustration on your problem
> and still don't have a solution. The chances of you getting a solution
> appears to be very dim. I would be glad to take a look at your problem and
> try and come up with a solution for a modest fee. I provide help with
> Access, Excel and Word applicayions for a small fee. If you want my help,
> contact me.
>
> Steve
> san...@penn.com

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past year to
show Stevie's "expertise".

Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve

Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve

Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve

One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Steve wrote:
> Yes, you are right but a database is the correct tool to use not a
> spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...

Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve

Sept 9, 2009
"Steve" <notmy...@address.com> wrote in message
> you can then return all the characters in front of it with the Left()
> fumction. Would look like:
> Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)
>
> Steve

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.

John... Visio MVP

[Scorpio11]

Hi Duane

I have been having a similar problem with the opening balance in a report, I have tried your suggested example of a Union Query in the Northwind.mdb template and it worked like a charm. I went and adapted it for my own ends , my own reports and even used the running balance, works perfectly.

Thank you. You have saved me lots of time and cursing..

> On Friday, September 11, 2009 7:48 AM 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!

>> On Friday, September 11, 2009 8:40 AM 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:

>>> On Friday, September 11, 2009 8:53 AM 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:

>>>> On Friday, September 11, 2009 9:15 AM Duane Hookom wrote:

>>>> First, IMO, I do not 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:

>>>>> On Friday, September 11, 2009 9:20 AM Difficult1 wrote:

>>>>> Hi, Duane. Thank you for your response. I did set up a form that asks for

>>>>> starting and ending dates, but, really was not sure where to go with it from

>>>>> there. It had 3 objects on it... Start Date, End Date and a Preview button.
>>>>> Is that the form I would put in place of frmDateSelect? Maybe I was halfway
>>>>> on the right track! I will give this a try and see where it gets me.
>>>>>
>>>>> Thank you so much!
>>>>>
>>>>> "Duane Hookom" wrote:

>>>>>> On Friday, September 11, 2009 10:05 AM Duane Hookom wrote:

>>>>>> If you already have a form to enter dates, you do not need to create a new one.

>>>>>>
>>>>>> --
>>>>>> Duane Hookom
>>>>>> Microsoft Access MVP
>>>>>>
>>>>>>
>>>>>> "Difficult1" wrote:

>>>>>>> On Friday, September 11, 2009 10:28 AM Duane Hookom wrote:

>>>>>>> A sample of how this can work with a union query, you can create a query in
>>>>>>> the Northwind sample mdb to sum Freight prior to a date as a beginning
>>>>>>> balance:
>>>>>>>
>>>>>>> SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, Freight
>>>>>>> FROM Orders
>>>>>>> WHERE OrderDate Between [Forms]![frmDateSelect]![txtStart] And
>>>>>>> [Forms]![frmDateSelect]![txtEnd]

>>>>>>> UNION

>>>>>>> SELECT 0, CustomerID,Null, Null, Null, Sum(Freight)
>>>>>>> FROM Orders
>>>>>>> WHERE OrderDate < [Forms]![frmDateSelect]![txtStart]
>>>>>>> GROUP BY CustomerID
>>>>>>> ORDER BY 2,1;
>>>>>>>

>>>>>>> --
>>>>>>> Duane Hookom
>>>>>>> Microsoft Access MVP
>>>>>>>
>>>>>>>
>>>>>>> "Difficult1" wrote:

>>>>>>>> On Friday, September 11, 2009 10:59 AM Difficult1 wrote:

>>>>>>>> Excellent. Let me give this a try and see how far I get. I set up the form

>>>>>>>> and then abandoned that idea because I was not sure how to bring it in. I

>>>>>>>> think your example gives me just what I need to make this work.
>>>>>>>>
>>>>>>>> Thank you!
>>>>>>>>
>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>> On Friday, September 11, 2009 2:02 PM 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:

>>>>>>>>>> On Friday, September 11, 2009 2:30 PM 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:

>>>>>>>>>>> On Friday, September 11, 2009 3:21 PM Difficult1 wrote:

>>>>>>>>>>> Ha! Yeah. I just threw Query2 there because I could not remember exactly what

>>>>>>>>>>> I called it. :-) Thanks a million! You are awesome.
>>>>>>>>>>>

>>>>>>>>>>> I wish I knew 1/8th of what you guys know when it comes to this stuff. I have

>>>>>>>>>>> learned to appreciate Access and its capabilities, but, have struggled with
>>>>>>>>>>> getting comfortable at learning some of the more challening bits. Excel has
>>>>>>>>>>> always been my software of choice, which is how I used to track the database
>>>>>>>>>>> I am now creating.
>>>>>>>>>>>
>>>>>>>>>>> Have a great weekend!
>>>>>>>>>>>
>>>>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>>>>> On Monday, September 14, 2009 8:11 AM Difficult1 wrote:

>>>>>>>>>>>> Thanks for taking the time to help me out with this. Unfortunately, I still

>>>>>>>>>>>> cannot 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 is all about

>>>>>>>>>>>> or how to fix it.
>>>>>>>>>>>>

>>>>>>>>>>>> I guess at this point I will go back to my Excel s/s and Crystal report combo.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>>>>>> On Monday, September 14, 2009 8:24 AM Duane Hookom wrote:

>>>>>>>>>>>>> Did you try the union query solution I suggested a while back?
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> Duane Hookom
>>>>>>>>>>>>> Microsoft Access MVP
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> "Difficult1" wrote:

>>>>>>>>>>>>>> On Monday, September 14, 2009 8:27 AM Difficult1 wrote:

>>>>>>>>>>>>>> Hi, Duane. I did. I did not get anywhere that way either. Maybe what I am

>>>>>>>>>>>>>> trying to do is way to advanced for me. I am probably forgetting something
>>>>>>>>>>>>>> absolutely stupid. I do appreciate the help though!
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>>>>>>>> On Monday, September 14, 2009 8:41 AM Duane Hookom wrote:

>>>>>>>>>>>>>>> You might want to share your union query SQL view with us and describe the
>>>>>>>>>>>>>>> results.
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>> Duane Hookom
>>>>>>>>>>>>>>> Microsoft Access MVP
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> "Difficult1" wrote:

>>>>>>>>>>>>>>>> On Monday, September 14, 2009 8:50 AM Difficult1 wrote:

>>>>>>>>>>>>>>>> SELECT ChildID,
>>>>>>>>>>>>>>>> FROM Transactions
>>>>>>>>>>>>>>>> WHERE Date Between [Forms]![frmDateRange]![FromDate] And
>>>>>>>>>>>>>>>> [Forms]![frmDateRange]![ToDate]
>>>>>>>>>>>>>>>> UNION SELECT 0, CustomerID,Null, Null, Null, Sum(BegBal)
>>>>>>>>>>>>>>>> FROM Transactions
>>>>>>>>>>>>>>>> WHERE Date < [Forms]![frmDateRange]![FromDate]
>>>>>>>>>>>>>>>> GROUP BY ChildID
>>>>>>>>>>>>>>>> ORDER BY 2, 1;
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> I get a message that says the number of columns do not match. I have never
>>>>>>>>>>>>>>>> done one of these before, so I feel kinda stupid.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 9:55 AM Duane Hookom wrote:

>>>>>>>>>>>>>>>>> As per the error message, the number of columns/fields in each of the SELECT
>>>>>>>>>>>>>>>>> clauses in a union query must be equal. Your first SELECT has only one column
>>>>>>>>>>>>>>>>> with a orphaned comma while your second SELECT has 6 columns.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Your first SELECT should have all of the fields needed in your report. The
>>>>>>>>>>>>>>>>> second select should be the GROUP BY query which will probably have Null for
>>>>>>>>>>>>>>>>> most of the columns. Do you actually have a BegBal field in the Transactions
>>>>>>>>>>>>>>>>> table? I would expect the second SELECT would SUM a transaction amount.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>> Duane Hookom
>>>>>>>>>>>>>>>>> Microsoft Access MVP
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> "Difficult1" wrote:

>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 11:44 AM Difficult1 wrote:

>>>>>>>>>>>>>>>>>> Okay, so, with some modifications, I am getting all of the information that I
>>>>>>>>>>>>>>>>>> need into my report. The only issue I am running into now is the dollar
>>>>>>>>>>>>>>>>>> amount in the BegBal calculation. I want it is sum everything before the
>>>>>>>>>>>>>>>>>> FromDate. It looks like it is using the same number for each individual
>>>>>>>>>>>>>>>>>> ($84), and not adding them up per ChildID before FromDate.
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> SELECT ChildID, ChildFirst, ChildLast, ParentFirst, ParentLast, BegBal,
>>>>>>>>>>>>>>>>>> Amount, PaymentAmt, StmtAmt, Description, Method, Date, Address1, City,
>>>>>>>>>>>>>>>>>> State, Zip, Hours, TimeIn, TimeOut, CheckNumber
>>>>>>>>>>>>>>>>>> FROM qTransbydate
>>>>>>>>>>>>>>>>>> WHERE Date Between [Forms]![frmDateRange]![FromDate] And
>>>>>>>>>>>>>>>>>> [Forms]![frmDateRange]![ToDate]
>>>>>>>>>>>>>>>>>> UNION SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
>>>>>>>>>>>>>>>>>> Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
>>>>>>>>>>>>>>>>>> FROM qTransbyDate
>>>>>>>>>>>>>>>>>> WHERE Date < [Forms]![frmDateRange]![FromDate]
>>>>>>>>>>>>>>>>>> GROUP BY ChildID
>>>>>>>>>>>>>>>>>> ORDER BY 2, 1;
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 1:37 PM Duane Hookom wrote:

>>>>>>>>>>>>>>>>>>> Good work. What do you see if you create a query with only this part of the
>>>>>>>>>>>>>>>>>>> UNION query:
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
>>>>>>>>>>>>>>>>>>> Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
>>>>>>>>>>>>>>>>>>> FROM qTransbyDate
>>>>>>>>>>>>>>>>>>> WHERE [Date] < [Forms]![frmDateRange]![FromDate]
>>>>>>>>>>>>>>>>>>> GROUP BY ChildID
>>>>>>>>>>>>>>>>>>> ORDER BY 2, 1;
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>> Duane Hookom
>>>>>>>>>>>>>>>>>>> Microsoft Access MVP
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> "Difficult1" wrote:

>>>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 1:59 PM Difficult1 wrote:

>>>>>>>>>>>>>>>>>>>> Still the same thing. Maybe I am putting the [BegBal] field in the wrong
>>>>>>>>>>>>>>>>>>>> place? I have a ChildID header with all the names and addresses, and that is
>>>>>>>>>>>>>>>>>>>> where I have it. Then in the ChildID detail section, I have all of my detail
>>>>>>>>>>>>>>>>>>>> for that month. It appears that the number coming into BegBal is the first
>>>>>>>>>>>>>>>>>>>> charge listed in the detail.
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> for example:
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> ChildID Header
>>>>>>>>>>>>>>>>>>>> BegBal = $84.00
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> ChildID Detail
>>>>>>>>>>>>>>>>>>>> 7/1/09 Daycare $84.00
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 2:27 PM Difficult1 wrote:

>>>>>>>>>>>>>>>>>>>>> Okay, so, in looking at this some more... I have found a couple of other
>>>>>>>>>>>>>>>>>>>>> things...
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> First, the beginning balance of the first ChildID in the query itself is

>>>>>>>>>>>>>>>>>>>>> correct -50.30. It is not pulling that same number into the report, it is
>>>>>>>>>>>>>>>>>>>>> pulling $84. The second ChildID does not have any records between those dates,
>>>>>>>>>>>>>>>>>>>>> so it is not bringing the header info in (Name, Address, so forth), but, it is

>>>>>>>>>>>>>>>>>>>>> bringing in the correct beginning balance (nothing else on the page). The
>>>>>>>>>>>>>>>>>>>>> third set of records that have test data should have a BegBal of -0- but it
>>>>>>>>>>>>>>>>>>>>> is showing $84 and the first line of detail is $84.
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> The query itself is showing me correct beggining balances and only the
>>>>>>>>>>>>>>>>>>>>> details I requested, with the exception of the name and address info for the
>>>>>>>>>>>>>>>>>>>>> one with -0- beggining balance.
>>>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>>>> Now I am really confused, but, I think I am sooooo very close to having this
>>>>>>>>>>>>>>>>>>>>> right.
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> "Difficult1" wrote:

>>>>>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 2:39 PM Duane Hookom wrote:

>>>>>>>>>>>>>>>>>>>>>> Why does not your union query select the Sum(StmtAmt) rather than some BegBal
>>>>>>>>>>>>>>>>>>>>>> field? I do not know where BegBal came from.

>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>>>> Duane Hookom
>>>>>>>>>>>>>>>>>>>>>> Microsoft Access MVP
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> "Difficult1" wrote:

>>>>>>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 2:52 PM Difficult1 wrote:

>>>>>>>>>>>>>>>>>>>>>>> The StmtAmt field is a formula field that combines charges and payments into
>>>>>>>>>>>>>>>>>>>>>>> one column. I have it set up that way because I use a drop down field that
>>>>>>>>>>>>>>>>>>>>>>> automatically fills a field with $84 or the number of hours x $4.20, so, it

>>>>>>>>>>>>>>>>>>>>>>> will not let me type a payment in there and override it.

>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> Trust me, I am just as confused by it. I think I am so close though.
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> "Duane Hookom" wrote:

>>>>>>>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 3:26 PM Duane Hookom wrote:

>>>>>>>>>>>>>>>>>>>>>>>> I think the key is where BegBal comes from. What is the SQL view of
>>>>>>>>>>>>>>>>>>>>>>>> qTransbyDate? How is StmtAmt calculated?
>>>>>>>>>>>>>>>>>>>>>>>>

>>>>>>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>>>>>> Duane Hookom
>>>>>>>>>>>>>>>>>>>>>>>> Microsoft Access MVP
>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>> "Difficult1" wrote:

>>>>>>>>>>>>>>>>>>>>>>>>> On Monday, September 14, 2009 5:10 PM Steve wrote:

>>>>>>>>>>>>>>>>>>>>>>>>> Hello!
>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>> You certrainly have spent a lot of time and frustration on your problem and

>>>>>>>>>>>>>>>>>>>>>>>>> still do not have a solution. The chances of you getting a solution appears

>>>>>>>>>>>>>>>>>>>>>>>>> to be very dim. I would be glad to take a look at your problem and try and
>>>>>>>>>>>>>>>>>>>>>>>>> come up with a solution for a modest fee. I provide help with Access, Excel
>>>>>>>>>>>>>>>>>>>>>>>>> and Word applicayions for a small fee. If you want my help, contact me.
>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>> Steve
>>>>>>>>>>>>>>>>>>>>>>>>> san...@penn.com

>>>>>>>>>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>>>>>>>>>>>>>>>> ASP.NET In-Memory Image Control with Built-In Resizing of Posted File
>>>>>>>>>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/e1a14e2c-e746-4bed-a552-24c632bd2709/aspnet-inmemory-image-control-with-builtin-resizing-of-posted-file.aspx