using dates in queries

  • Thread starter Thread starter iain
  • Start date Start date
I

iain

Hi,

Can anyone let me know if the following is possible?

When you do a query that filters out records based on a
date field where the records fall between any two dates,
ie in the relevant date field in the query you have
something like :

Between [Earliest date in range?] And [Latest date in
range?]

as a prompt to the user, can you then have the dates
entered by the user in a report based on that query?

ie as part of the report title / page header, it would say
something like *Developments Plotted between [Earliest
date in range] and [Latest date in range]*?

Hope that makes sense,

Cheers,
Iain
 
Iain,

What you can do is use the "openreport" action in a
macro to open the report you are interested in. First
create the macro, have it use the openreport action to
open the report you are interested in. Then create a form
(if you already don't have one), and place a button on the
form. In the onclick property of that button on the form,
assign the macro that you created.

Then, make sure that for the source of the report
being called by your macro that you had built, that the
source for that report is the query that you are
interested in. Also, make sure that in the column of that
query that holds the date field, that the Between [Please
enter start date] and [Please enter end date] are in the
criteria for that date field.

Now, after you have gotten that far, open your form,
press your button that calls the report using your macro,
and before the report opens up, the prompt will first
appear as "Please enter start date", the user should then
enter the start date using slashes. After that, the
prompt will appear with "Please enter end date", and the
user should then enter the end date using slashes. Then
the report will open with the information that happened
between the dates that the user specified.

That is a way to do it.

I hope I helped you, and have a nice day.

Casey
 
Hi,

Thanks for that - but i think i've already gotten that far.

What i'd like it to do, is have the dates the user enters
in the prompts to actually appear in the report, as well
as the actual dates in the fields.

If you see what I mean.

Iain

-----Original Message-----
Iain,

What you can do is use the "openreport" action in a
macro to open the report you are interested in. First
create the macro, have it use the openreport action to
open the report you are interested in. Then create a form
(if you already don't have one), and place a button on the
form. In the onclick property of that button on the form,
assign the macro that you created.

Then, make sure that for the source of the report
being called by your macro that you had built, that the
source for that report is the query that you are
interested in. Also, make sure that in the column of that
query that holds the date field, that the Between [Please
enter start date] and [Please enter end date] are in the
criteria for that date field.

Now, after you have gotten that far, open your form,
press your button that calls the report using your macro,
and before the report opens up, the prompt will first
appear as "Please enter start date", the user should then
enter the start date using slashes. After that, the
prompt will appear with "Please enter end date", and the
user should then enter the end date using slashes. Then
the report will open with the information that happened
between the dates that the user specified.

That is a way to do it.

I hope I helped you, and have a nice day.

Casey
-----Original Message-----
Hi,

Can anyone let me know if the following is possible?

When you do a query that filters out records based on a
date field where the records fall between any two dates,
ie in the relevant date field in the query you have
something like :

Between [Earliest date in range?] And [Latest date in
range?]

as a prompt to the user, can you then have the dates
entered by the user in a report based on that query?

ie as part of the report title / page header, it would say
something like *Developments Plotted between [Earliest
date in range] and [Latest date in range]*?

Hope that makes sense,

Cheers,
Iain


.
Click to expand...
.
Click to expand...
 
Iain,

Then what you want to do is have the date entries that
you accept from the user each have a text box on a form
for the entries. Then, in your report query for the
criteria for the date field, iterate something similar to
as follows:

between forms.formname.textbox1 and forms.formname.textbox2

But you would use your real form name for the
formname, and real names of your text boxes.

Then in your report, where you want the dates to
appear, create text boxes. For the "Control Source"
property of each of the text boxes place

= forms.formname.textbox1

for the first box

and

= forms.formname.textbox2

for the second box

Use your real form name and text box names from the
form instead of what I used. What this does is refer to
the values on your form and places them in your report for
you.

Casey




-----Original Message-----
Hi,

Thanks for that - but i think i've already gotten that far.

What i'd like it to do, is have the dates the user enters
in the prompts to actually appear in the report, as well
as the actual dates in the fields.

If you see what I mean.

Iain

-----Original Message-----
Iain,

What you can do is use the "openreport" action in a
macro to open the report you are interested in. First
create the macro, have it use the openreport action to
open the report you are interested in. Then create a form
(if you already don't have one), and place a button on the
form. In the onclick property of that button on the form,
assign the macro that you created.

Then, make sure that for the source of the report
being called by your macro that you had built, that the
source for that report is the query that you are
interested in. Also, make sure that in the column of that
query that holds the date field, that the Between [Please
enter start date] and [Please enter end date] are in the
criteria for that date field.

Now, after you have gotten that far, open your form,
press your button that calls the report using your macro,
and before the report opens up, the prompt will first
appear as "Please enter start date", the user should then
enter the start date using slashes. After that, the
prompt will appear with "Please enter end date", and the
user should then enter the end date using slashes. Then
the report will open with the information that happened
between the dates that the user specified.

That is a way to do it.

I hope I helped you, and have a nice day.

Casey
-----Original Message-----
Hi,

Can anyone let me know if the following is possible?

When you do a query that filters out records based on a
date field where the records fall between any two dates,
ie in the relevant date field in the query you have
something like :

Between [Earliest date in range?] And [Latest date in
range?]

as a prompt to the user, can you then have the dates
entered by the user in a report based on that query?

ie as part of the report title / page header, it would say
something like *Developments Plotted between [Earliest
date in range] and [Latest date in range]*?

Hope that makes sense,

Cheers,
Iain


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Rarely used but you can actually put a TextBox Control on the Report with
the ControlSource set to:

= "Developments Plotted between " & [Earliest date in range?] &
" and " & [Latest date in ranges?]

Notes:
1. Type as ONE line.
2. Including the equal sign in the ControlSource.
3. The question mark is part of the Parameter names so you must include the
question mark when you refer to the Parameter by its name.
 
brilliant - thanks for that - exactly what i was after.

Iain
-----Original Message-----
Rarely used but you can actually put a TextBox Control on the Report with
the ControlSource set to:

= "Developments Plotted between " & [Earliest date in range?] &
" and " & [Latest date in ranges?]

Notes:
1. Type as ONE line.
2. Including the equal sign in the ControlSource.
3. The question mark is part of the Parameter names so you must include the
question mark when you refer to the Parameter by its name.

--
HTH
Van T. Dinh
MVP (Access)




iain said:
Hi,

Can anyone let me know if the following is possible?

When you do a query that filters out records based on a
date field where the records fall between any two dates,
ie in the relevant date field in the query you have
something like :

Between [Earliest date in range?] And [Latest date in
range?]

as a prompt to the user, can you then have the dates
entered by the user in a report based on that query?

ie as part of the report title / page header, it would say
something like *Developments Plotted between [Earliest
date in range] and [Latest date in range]*?

Hope that makes sense,

Cheers,
Iain
Click to expand...


.
Click to expand...
 
Back
Top