Command Button Pt2

[MikeT]

Thanks fredg and Al Campagna for you help on my first post.

Following on from the first post, I would be gratful if anyone could advise
how i can open a report but only show entries between two dates using a
command button?

Using fredg's help with my first post, i now have the following expression
which enables me to use a command button to open a report based on the
current open record:

DoCmd.OpenReport stDocName, acViewPreview, , "[RecordID]= " & Me.[RecordID]

how do i now extend that to allow me to use the same report, but only show
entries between two dates that are recorded on a seperate form (Form Name
RegDates)? RegDates contains a number of entries relating to Registration of
Events that will take place between two set dates, these are entered into
individual fields and are manually input, so for agruments sake open and
close dates, but i need my report to show me all the entries made between the
open and close dates.

Thanks for your help.

Mike

 

[Arvin Meyer MVP]

It is possible, to code the report to open directly, but it is better to use
a query as the recordsource for the report and change the dates that the
query uses.

What you need to do is to use a dialog form or menu with the command button,
and 2 text boxes named txtStart and txtEnd, then in the underlying query,
add the following criteria to the datefield column:

Between [Forms]![MenuFormName]![txtStart] And
[Forms]![MenuFormName]![txtEnd]

Fill in any 2 dates in the MenuForm and click the button to open the report.

 

[MikeT]

Hi Thanks for getting back to me,

I do use a query to produce the report, but currently the data is sorted by
critera ie:

Between [Type Start Date:] And [Type End Date:]

obviously this requires manual intervention. I'm not familiar with the
terms dialog form/Menu, are these found in the properties of the command
button itself or are you saying i need to create a new form and add the
txtStart and txtEnd dates to this separate form?

You have also stated that I just need to enter any two dates, once this is
done, however would this work if i have a number of projects running
simultanioulsy with different start and finish dates? Would it not just
produce the report between to the two dates?

All i need to do is open a query report, but use dates that have already
been fixed on a different form.

Sorry i am not a pro with access terminology, so i have probably missed the
point.

Mike

It is possible, to code the report to open directly, but it is better to use
a query as the recordsource for the report and change the dates that the
query uses.

What you need to do is to use a dialog form or menu with the command button,
and 2 text boxes named txtStart and txtEnd, then in the underlying query,
add the following criteria to the datefield column:

Between [Forms]![MenuFormName]![txtStart] And
[Forms]![MenuFormName]![txtEnd]

Fill in any 2 dates in the MenuForm and click the button to open the report.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks fredg and Al Campagna for you help on my first post.

Following on from the first post, I would be gratful if anyone could
advise
how i can open a report but only show entries between two dates using a
command button?

Using fredg's help with my first post, i now have the following expression
which enables me to use a command button to open a report based on the
current open record:

DoCmd.OpenReport stDocName, acViewPreview, , "[RecordID]= " &
Me.[RecordID]

how do i now extend that to allow me to use the same report, but only show
entries between two dates that are recorded on a seperate form (Form Name
RegDates)? RegDates contains a number of entries relating to Registration
of
Events that will take place between two set dates, these are entered into
individual fields and are manually input, so for agruments sake open and
close dates, but i need my report to show me all the entries made between
the
open and close dates.

Thanks for your help.

Mike

 

[Arvin Meyer MVP]

OK,

An unbound form, this is, a form which does not have a recordsource (like a
menu form), with 2 unbound text boxes to hold the date to be passed to the
query.

If the start and finish dates are in separate field in the table, you have
multiple considerations, for which you must construct separarate queries, or
use separate OR criteria. For instance, current projects are projects which
don't have a finish date. You'd use my expression below in the Start date
field. Finished projects may require you to put the criteria in the
FinishDate field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Thanks for getting back to me,

I do use a query to produce the report, but currently the data is sorted
by
critera ie:

Between [Type Start Date:] And [Type End Date:]

obviously this requires manual intervention. I'm not familiar with the
terms dialog form/Menu, are these found in the properties of the command
button itself or are you saying i need to create a new form and add the
txtStart and txtEnd dates to this separate form?

You have also stated that I just need to enter any two dates, once this is
done, however would this work if i have a number of projects running
simultanioulsy with different start and finish dates? Would it not just
produce the report between to the two dates?

All i need to do is open a query report, but use dates that have already
been fixed on a different form.

Sorry i am not a pro with access terminology, so i have probably missed
the
point.

Mike

It is possible, to code the report to open directly, but it is better to
use
a query as the recordsource for the report and change the dates that the
query uses.

What you need to do is to use a dialog form or menu with the command
button,
and 2 text boxes named txtStart and txtEnd, then in the underlying query,
add the following criteria to the datefield column:

Between [Forms]![MenuFormName]![txtStart] And
[Forms]![MenuFormName]![txtEnd]

Fill in any 2 dates in the MenuForm and click the button to open the
report.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks fredg and Al Campagna for you help on my first post.

Following on from the first post, I would be gratful if anyone could
advise
how i can open a report but only show entries between two dates using a
command button?

Using fredg's help with my first post, i now have the following
expression
which enables me to use a command button to open a report based on the
current open record:

DoCmd.OpenReport stDocName, acViewPreview, , "[RecordID]= " &
Me.[RecordID]

how do i now extend that to allow me to use the same report, but only
show
entries between two dates that are recorded on a seperate form (Form
Name
RegDates)? RegDates contains a number of entries relating to
Registration
of
Events that will take place between two set dates, these are entered
into
individual fields and are manually input, so for agruments sake open
and
close dates, but i need my report to show me all the entries made
between
the
open and close dates.

Thanks for your help.

Mike