Producing a form that shows totals for a date range

[Guest]

I have a simple DB that records income and expenses for my business. I have
created a form that shows all my transactions and then a summary of the total
expenses, total income and gross profit. I want to be able to view this
information for a variety of date ranges, but have been unable to work out
how to do this. At the moment I can only get a total for all transactions,
even if I use criteria to limit the date range. I'm sure this should be
simple to do but I can't figure it out.

All help gratefully received.

 

[Ken Snell \(MVP\)]

Are you wanting to show the totals for a specific date range on the form at
all times? Or do you want to "select" a date range while you're on the form
and have the form display a total for your selected date range?

Tell us a bit more about your form, its RecordSource query, and what you
want to be able to do.

 

[Guest]

Thanks for the response. I would like to select a date range and have the
form dislpay the relevant totals for that range. At the moment my form has a
SubForm using information from a query called Transaction Query that just
lists all transactions, regardless of date or Transaction type. I have then
just added text boxes to the top of the form that Sum the Expense and Income
fields. Whilst this has certain uses (I have a very straightforward
business) I really need to be able to view that data (and relevant totals) by
date and Transaction type as well if possible.

Does this make sense??

 

[Ken Snell \(MVP\)]

Yes, what you want to do makes sense.

One more question to clarify...

Do you want to select the date range on the same form that then displays the
data? or do you want to select the date range on another form and then have
that form open the form that displays the data?

 

[Guest]

Ken

I think selecting the date from the form that displays the result will suit
me fine. Thanks again for your responses.

Richard

 

[Guest]

Another thing to consider, is if when you sort the data on your form, do you
also want the transactions in the Subform filtered by date? You mentioned
that right now they are queried regardless of date.

I ran into this same issue when I wanted to sort data by date and it
involved records in a subform.

 

[Ken Snell \(MVP\)]

OK -

Easiest way is to add a criterion expression to your subform's RecordSource
query. Open that query and go to the Criteria: box under the date field from
the table. Type this in that box:

Between Nz(Forms!FormName!StartDateControlName, 0) And
Nz(Forms!FormName!EndDateControlName, Date())

(Replace my generic names of FormName, StartDateControlName, and
EndDateControlName) with the real names. Save the query.

Then put a command button on the main form next to the textboxes where you
will be able to type in the start and end dates. On this button's Click
event, put this VBA code:

Private CommandButtonName_Click()
Me.Subform.Requery
End Sub

where Subform is the name of the subform control (the control that holds the
subform object) on the form.

Now, you'll be able to put a date in one or both textboxes, and then click
the button to filter the subform. In the above criterion expression, I'm
using the Nz function to automatically use a date value of 0 (meaning, the
very earliest date possible) if no value is in the start date textbox, and a
value of today's date if no value is in the end date textbox.

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Thanks. I think that makes sense!! I will have a go and see how get on.
Many thanks for your help.

Richard

 

[Guest]

I've had a try with your suggestion but I don't think I have the Access
knowledge to put it into practice. Thanks for your help anyway.

Richard

 

[Ken Snell \(MVP\)]

We're here to help.... how far did you get? How can we assist?

 

[Guest]

When I open my form it immediately prompts me for 2 sets of dates. Once I
have entered the dates the form will show the records for that range but I
have to enter the dates twice! Entering dates in the text boxes I have added
and using the command button has no effect at all. This is what I have
entered in the click event for the command button;

Private FilterbyDate_Click()
Me.TransactionQuery.Requery
End Sub

FilterbyDate is the command button name and TransactionQuery is the query
that is used by the Subform. What I have done also appears to have upset my
total profit text box that now comes up with #Name? rather than performing
the calculation that is previously did without any problem! For your
information, this is what I have entered into the TransactionDate field of my
query;

Between nz([Forms]![ProfitAnalysis]![StartDate],0) And
nz([Forms]![ProfitAnalysis]![EndDate],Date())

Can you help me get out of this tangle??

 

[Ken Snell \(MVP\)]

The prompting when you open the form initially is "my fault"... subforms
are loaded before the main form and therefore the query being used by the
subform cannot "see" the main form when the subform is being opened --
therefore you get that initial prompt. This can be avoided by a small change
in the programming.

But before we get to that, let's nail down that you're using the correct
name for the subform in your button's code.

Open the main form in design view. Click on the very top edge of the subform
control. Open the Properties window. Click on the Other tab. What name is in
the Name textbox?
--

Ken Snell
<MS ACCESS MVP>

When I open my form it immediately prompts me for 2 sets of dates. Once I
have entered the dates the form will show the records for that range but I
have to enter the dates twice! Entering dates in the text boxes I have
added
and using the command button has no effect at all. This is what I have
entered in the click event for the command button;

Private FilterbyDate_Click()
Me.TransactionQuery.Requery
End Sub

FilterbyDate is the command button name and TransactionQuery is the query
that is used by the Subform. What I have done also appears to have upset
my
total profit text box that now comes up with #Name? rather than performing
the calculation that is previously did without any problem! For your
information, this is what I have entered into the TransactionDate field of
my
query;

Between nz([Forms]![ProfitAnalysis]![StartDate],0) And
nz([Forms]![ProfitAnalysis]![EndDate],Date())

Can you help me get out of this tangle??

We're here to help.... how far did you get? How can we assist?

 

[Guest]

The name in the Subform properties is "Child1". I assume that this is the
name that the code for the button needs to refer to??

The prompting when you open the form initially is "my fault"... subforms
are loaded before the main form and therefore the query being used by the
subform cannot "see" the main form when the subform is being opened --
therefore you get that initial prompt. This can be avoided by a small change
in the programming.

But before we get to that, let's nail down that you're using the correct
name for the subform in your button's code.

Open the main form in design view. Click on the very top edge of the subform
control. Open the Properties window. Click on the Other tab. What name is in
the Name textbox?
--

Ken Snell
<MS ACCESS MVP>

When I open my form it immediately prompts me for 2 sets of dates. Once I
have entered the dates the form will show the records for that range but I
have to enter the dates twice! Entering dates in the text boxes I have
added
and using the command button has no effect at all. This is what I have
entered in the click event for the command button;

Private FilterbyDate_Click()
Me.TransactionQuery.Requery
End Sub

FilterbyDate is the command button name and TransactionQuery is the query
that is used by the Subform. What I have done also appears to have upset
my
total profit text box that now comes up with #Name? rather than performing
the calculation that is previously did without any problem! For your
information, this is what I have entered into the TransactionDate field of
my
query;

Between nz([Forms]![ProfitAnalysis]![StartDate],0) And
nz([Forms]![ProfitAnalysis]![EndDate],Date())

Can you help me get out of this tangle??

:

 

[Ken Snell \(MVP\)]

Yep, Child1 is the name that you should use in the button's click event
procedure < smile >.

So, let's make these changes to the rest of the form in order to eliminate
that initial prompt and to make things work the way you want. I am assuming
here that the record source for the subform is TransactionQuery.

Open the main form in design view.

Go to the code for that button that you now have. Change its code to this:

Private FilterbyDate_Click()
If Me.Child1.Form.RecordSource = "" Then
Me.Child1.Form.RecordSource = "TransactionQuery"
Else
Me.Child1.Requery
End If
End Sub.

Then click on the subform ("Child1") in the design view. Then click on box
at top left of subform so that we can edit the subform's source object form.
Then open Properties box and select the Data tab for the Form object of the
subform. Delete TransactionQuery from the RecordSource box.

Now save the forms. Close them.

Now the forms should work the way you want. What we're doing is to have the
subform open without a recordsource, which means it won't ask you for the
dates. Then we're using the filter button to set the record source if it's
still an empty string, which requeries the subform; or else, if the
recordsource is already there, just requery the subform.

--

Ken Snell
<MS ACCESS MVP>

The name in the Subform properties is "Child1". I assume that this is the
name that the code for the button needs to refer to??

The prompting when you open the form initially is "my fault"... subforms
are loaded before the main form and therefore the query being used by the
subform cannot "see" the main form when the subform is being opened --
therefore you get that initial prompt. This can be avoided by a small
change
in the programming.

But before we get to that, let's nail down that you're using the correct
name for the subform in your button's code.

Open the main form in design view. Click on the very top edge of the
subform
control. Open the Properties window. Click on the Other tab. What name is
in
the Name textbox?
--

Ken Snell
<MS ACCESS MVP>

When I open my form it immediately prompts me for 2 sets of dates.
Once I
have entered the dates the form will show the records for that range
but I
have to enter the dates twice! Entering dates in the text boxes I have
added
and using the command button has no effect at all. This is what I have
entered in the click event for the command button;

Private FilterbyDate_Click()
Me.TransactionQuery.Requery
End Sub

FilterbyDate is the command button name and TransactionQuery is the
query
that is used by the Subform. What I have done also appears to have
upset
my
total profit text box that now comes up with #Name? rather than
performing
the calculation that is previously did without any problem! For your
information, this is what I have entered into the TransactionDate field
of
my
query;

Between nz([Forms]![ProfitAnalysis]![StartDate],0) And
nz([Forms]![ProfitAnalysis]![EndDate],Date())

Can you help me get out of this tangle??

:

 

[Guest]

Ken,

Many thanks for your help, think I've got it working how I want now! Didn't
realise it would be quite so tricky to do what I wanted. Thanks again for
your help.

Richard

Yep, Child1 is the name that you should use in the button's click event
procedure < smile >.

So, let's make these changes to the rest of the form in order to eliminate
that initial prompt and to make things work the way you want. I am assuming
here that the record source for the subform is TransactionQuery.

Open the main form in design view.

Go to the code for that button that you now have. Change its code to this:

Private FilterbyDate_Click()
If Me.Child1.Form.RecordSource = "" Then
Me.Child1.Form.RecordSource = "TransactionQuery"
Else
Me.Child1.Requery
End If
End Sub.

Then click on the subform ("Child1") in the design view. Then click on box
at top left of subform so that we can edit the subform's source object form.
Then open Properties box and select the Data tab for the Form object of the
subform. Delete TransactionQuery from the RecordSource box.

Now save the forms. Close them.

Now the forms should work the way you want. What we're doing is to have the
subform open without a recordsource, which means it won't ask you for the
dates. Then we're using the filter button to set the record source if it's
still an empty string, which requeries the subform; or else, if the
recordsource is already there, just requery the subform.

--

Ken Snell
<MS ACCESS MVP>

The name in the Subform properties is "Child1". I assume that this is the
name that the code for the button needs to refer to??

The prompting when you open the form initially is "my fault"... subforms
are loaded before the main form and therefore the query being used by the
subform cannot "see" the main form when the subform is being opened --
therefore you get that initial prompt. This can be avoided by a small
change
in the programming.

But before we get to that, let's nail down that you're using the correct
name for the subform in your button's code.

Open the main form in design view. Click on the very top edge of the
subform
control. Open the Properties window. Click on the Other tab. What name is
in
the Name textbox?
--

Ken Snell
<MS ACCESS MVP>



message When I open my form it immediately prompts me for 2 sets of dates.
Once I
have entered the dates the form will show the records for that range
but I
have to enter the dates twice! Entering dates in the text boxes I have
added
and using the command button has no effect at all. This is what I have
entered in the click event for the command button;

Private FilterbyDate_Click()
Me.TransactionQuery.Requery
End Sub

FilterbyDate is the command button name and TransactionQuery is the
query
that is used by the Subform. What I have done also appears to have
upset
my
total profit text box that now comes up with #Name? rather than
performing
the calculation that is previously did without any problem! For your
information, this is what I have entered into the TransactionDate field
of
my
query;

Between nz([Forms]![ProfitAnalysis]![StartDate],0) And
nz([Forms]![ProfitAnalysis]![EndDate],Date())

Can you help me get out of this tangle??

:

 

[Ken Snell \(MVP\)]

You're welcome. By the way, the approach that I suggested is just one way to
do what you seek. You also could do similar actions by programatically
applying a filter to the subform's data, or by programmatically rewriting
the subform's RecordSource query string, or .....

Each has its pros and cons. When I first started with ACCESS, I used the
approach that I suggested for you... now that I'm "deep" into ACCESS, I
usually rewrite the RecordSource strings....