Access Opening Balance Expression

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

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

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

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;


:

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


:

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.


:

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


:

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!

:

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

--
Duane Hookom
Microsoft Access MVP


:

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.


:

"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


:

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?

:

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


:

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.

:

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


:

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]

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

--
Duane Hookom
Microsoft Access MVP

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.

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

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


:

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;


:

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


:

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.


:

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


:

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!

:

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

--
Duane Hookom
Microsoft Access MVP


:

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.


:

"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


:

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?

:

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


:

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.

:

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


:

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!

 

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

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

--
Duane Hookom
Microsoft Access MVP

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.

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


:

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


:

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;


:

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


:

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.


:

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


:

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!

:

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

--
Duane Hookom
Microsoft Access MVP


:

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.


:

"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


:

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?

:

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


:

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.

:

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


:

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]

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

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.

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

--
Duane Hookom
Microsoft Access MVP

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.

:

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


:

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


:

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;


:

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


:

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.


:

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


:

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!

:

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

--
Duane Hookom
Microsoft Access MVP


:

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.


:

"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


:

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?

:

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


:

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.

:

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


:

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!

 

[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
(e-mail address removed)

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.

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

--
Duane Hookom
Microsoft Access MVP

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.

:

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


:

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


:

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;


:

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


:

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.


:

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


:

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!

:

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

--
Duane Hookom
Microsoft Access MVP


:

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.


:

"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


:

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?

:

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


:

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.

:

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


:

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!

 

[John... Visio MVP]

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
(e-mail address removed)

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)

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

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