Date Ranges in Report / Query

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

On Wed, 20 Dec 2006 12:56:02 -0800, FJquestioner wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates.
Click the command button and the report will run.
When the report closes, it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I use two text boxes on a form formatted for short date. You can use a
calendar or manually enter your dates however you prefer.

in your query's date field critera

use Between forms!myform!startdatetextbox and forms!myform!enddatetexbox
--
Jeff C
Live Well .. Be Happy In All You Do


"FJquestioner" wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Thanks for the fast response. I'll give it a try.
FJ

"fredg" wrote:

On Wed, 20 Dec 2006 12:56:02 -0800, FJquestioner wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates.
Click the command button and the report will run.
When the report closes, it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks. I'll try that...

"Jeff C" wrote:

I use two text boxes on a form formatted for short date. You can use a
calendar or manually enter your dates however you prefer.

in your query's date field critera

use Between forms!myform!startdatetextbox and forms!myform!enddatetexbox
--
Jeff C
Live Well .. Be Happy In All You Do


"FJquestioner" wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

I've followed your advice and if I run the query for the report it works
fine. But when I run the report itself, the form for entering the dates
doesn't pop up. Instead I get this error message:

Microsoft Access can't find the macro DoCmd

To code the [ DoCmd.OpenForm "ParamForm", , , , , acDialog ] as you
suggested, all I did was copy that line into the On Open event for the
Report. I didn't create a macro.

Did I do something wrong?

Thanks again,

FJ



"fredg" wrote:

On Wed, 20 Dec 2006 12:56:02 -0800, FJquestioner wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates.
Click the command button and the report will run.
When the report closes, it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Wed, 20 Dec 2006 15:14:01 -0800, FJquestioner wrote:

I've followed your advice and if I run the query for the report it works
fine. But when I run the report itself, the form for entering the dates
doesn't pop up. Instead I get this error message:

Microsoft Access can't find the macro DoCmd

To code the [ DoCmd.OpenForm "ParamForm", , , , , acDialog ] as you
suggested, all I did was copy that line into the On Open event for the
Report. I didn't create a macro.

Did I do something wrong?

Thanks again,

FJ

"fredg" wrote:

On Wed, 20 Dec 2006 12:56:02 -0800, FJquestioner wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates.
Click the command button and the report will run.
When the report closes, it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


The code doesn't go on the Open event line.

Here is how to write code.

On the line that says On Open write:
[Event Procedure]

Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

make sure it's spelled exactly as above, and you include the 5 commas.
Close the code window.

Then click on the On Close line and do the same thing as above, except
you will write:

DoCmd.Close acForm, "ParamForm"

in the code window.
Close the window and save.

Run the report.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Great, thanks. It worked like a charm !

FJ

"fredg" wrote:

On Wed, 20 Dec 2006 15:14:01 -0800, FJquestioner wrote:

I've followed your advice and if I run the query for the report it works
fine. But when I run the report itself, the form for entering the dates
doesn't pop up. Instead I get this error message:

Microsoft Access can't find the macro DoCmd

To code the [ DoCmd.OpenForm "ParamForm", , , , , acDialog ] as you
suggested, all I did was copy that line into the On Open event for the
Report. I didn't create a macro.

Did I do something wrong?

Thanks again,

FJ

"fredg" wrote:

On Wed, 20 Dec 2006 12:56:02 -0800, FJquestioner wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates.
Click the command button and the report will run.
When the report closes, it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


The code doesn't go on the Open event line.

Here is how to write code.

On the line that says On Open write:
[Event Procedure]

Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

make sure it's spelled exactly as above, and you include the 5 commas.
Close the code window.

Then click on the On Close line and do the same thing as above, except
you will write:

DoCmd.Close acForm, "ParamForm"

in the code window.
Close the window and save.

Run the report.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

After following your suggestion below---

On the line that says On Open write: [Event Procedure]
Then click on the little button with the 3 dots that appears on that line.
When the code
window opens, the cursor will be flashing between 2 already existing lines
of code.
Between those 2 lines, write: DoCmd.OpenForm "ParamForm", , , , , acDialog

Then click on the On Close line and do the same thing as above, except you
will write:
DoCmd.Close acForm, "ParamForm" in the code window. Close the window and
save.


---my report now opens the form to select the dates, but when I select the
command box, I receive the following error:

Microsoft Office Access can't find the macro 'Visable=False' in the macro
group 'Me'

Do I need to create a macro to run the command button suggested below:
Add a Command Button to the form. Code the button's click event: Me.Visible
= False
If so, what should be included in the macro?


"FJquestioner" wrote:

Great, thanks. It worked like a charm !

FJ

"fredg" wrote:

On Wed, 20 Dec 2006 15:14:01 -0800, FJquestioner wrote:

I've followed your advice and if I run the query for the report it works
fine. But when I run the report itself, the form for entering the dates
doesn't pop up. Instead I get this error message:

Microsoft Access can't find the macro DoCmd

To code the [ DoCmd.OpenForm "ParamForm", , , , , acDialog ] as you
suggested, all I did was copy that line into the On Open event for the
Report. I didn't create a macro.

Did I do something wrong?

Thanks again,

FJ

"fredg" wrote:

On Wed, 20 Dec 2006 12:56:02 -0800, FJquestioner wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates.
Click the command button and the report will run.
When the report closes, it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


The code doesn't go on the Open event line.

Here is how to write code.

On the line that says On Open write:
[Event Procedure]

Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

make sure it's spelled exactly as above, and you include the 5 commas.
Close the code window.

Then click on the On Close line and do the same thing as above, except
you will write:

DoCmd.Close acForm, "ParamForm"

in the code window.
Close the window and save.

Run the report.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I am no longer receiving the error message, but now, when the report opens,
it is blank.


"CMTH" wrote:

After following your suggestion below---

On the line that says On Open write: [Event Procedure]
Then click on the little button with the 3 dots that appears on that line.
When the code
window opens, the cursor will be flashing between 2 already existing lines
of code.
Between those 2 lines, write: DoCmd.OpenForm "ParamForm", , , , , acDialog

Then click on the On Close line and do the same thing as above, except you
will write:
DoCmd.Close acForm, "ParamForm" in the code window. Close the window and
save.


---my report now opens the form to select the dates, but when I select the
command box, I receive the following error:

Microsoft Office Access can't find the macro 'Visable=False' in the macro
group 'Me'

Do I need to create a macro to run the command button suggested below:
Add a Command Button to the form. Code the button's click event: Me.Visible
= False
If so, what should be included in the macro?


"FJquestioner" wrote:

Great, thanks. It worked like a charm !

FJ

"fredg" wrote:

On Wed, 20 Dec 2006 15:14:01 -0800, FJquestioner wrote:

I've followed your advice and if I run the query for the report it works
fine. But when I run the report itself, the form for entering the dates
doesn't pop up. Instead I get this error message:

Microsoft Access can't find the macro DoCmd

To code the [ DoCmd.OpenForm "ParamForm", , , , , acDialog ] as you
suggested, all I did was copy that line into the On Open event for the
Report. I didn't create a macro.

Did I do something wrong?

Thanks again,

FJ

"fredg" wrote:

On Wed, 20 Dec 2006 12:56:02 -0800, FJquestioner wrote:

I have a table of invoices and a report based on it. I'd like to make a
report/query based on invoices of a date range specified by the user (eg. all
invoices dated Jan 5 to Apr3). How do I fit that date range into the query
and how does the user go about entering the 2 dates after the report is
selected?

I have no problem getting the query to select invoices greater or equal to a
given date =[Enter Date] but I can't get it to handle both the start
and end dates....

Thanks
FJ

Create an unbound form.
Add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

As criteria in the query date field write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the entry of the starting and ending
dates.
Click the command button and the report will run.
When the report closes, it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


The code doesn't go on the Open event line.

Here is how to write code.

On the line that says On Open write:
[Event Procedure]

Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

make sure it's spelled exactly as above, and you include the 5 commas.
Close the code window.

Then click on the On Close line and do the same thing as above, except
you will write:

DoCmd.Close acForm, "ParamForm"

in the code window.
Close the window and save.

Run the report.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail