Connecting parameter in form to query for report display

[Guest]

I followed a recommendation for achieving the above-mentioned project by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I wrote a
report that prompts the user to enter the parameter in a form, the form sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the form
prompt pops up, I make the selection in the form prompt, see the query
pulling up the correct data but continue to get "compile error: variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions. I get
the same result when I try to run the Sales by Category report that is given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to achieve
what I want? All assistance is prematurely appreciated.

 

[Pieter Wijnen]

Simple; Access 2000 Doesn't know "acCurViewDesign" for Reports, only Forms
You can try SysCmd acSysCmdObjectState I think.
Never bothered with Access 2000 (too many bugs)

HTH

Pieter

 

[Bob Quintal]

I followed a recommendation for achieving the above-mentioned
project by following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx.
I wrote a report that prompts the user to enter the parameter
in a form, the form sends the selection to the query and
displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the
report, the form prompt pops up, I make the selection in the
form prompt, see the query pulling up the correct data but
continue to get "compile error: variable not defined" in the
module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section
of the instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the
instructions. I get the same result when I try to run the
Sales by Category report that is given as an example. FYI -
I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it
to achieve what I want? All assistance is prematurely
appreciated.

replace the acCurViewDesign constant with a 0.

or better yet, replace Isloaded with the one found here
http://www.mvps.org/access/forms/frm0002.htm

not it is called fIsLoaded, not Isloaded, so alter the name in
the form or the code.

 

[fredg]

I followed a recommendation for achieving the above-mentioned project by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I wrote a
report that prompts the user to enter the parameter in a form, the form sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the form
prompt pops up, I make the selection in the form prompt, see the query
pulling up the correct data but continue to get "compile error: variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions. I get
the same result when I try to run the Sales by Category report that is given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to achieve
what I want? All assistance is prematurely appreciated.

I have no idea what the directions given were, nor, of course, what
you have or haven't done to implement them.

Here is my method, which you are welcome to adapt and use.

It uses a combo box to select a Company, as well as 2 controls to
enter date parameters. Adapt as needed.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also 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'.

In the Query, (the Report's Record Source) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

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 selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

To display the date parameters in the report, add an unbound text
control to the Report Header.
Set it's control source to:
="For sales between " & [forms!Paramform!StartDate] & " and " &
[forms!ParamForm!EndDate]

The text, i.e. forms!Paramform!StartDate & forms!ParamForm!EndDate,
must be identical to the text in the query. Include the brackets as
shown.

 

[Guest]

Thanks Pieter,
That's what I thought.
I took your advice - replaced acCurViewDesign with SysCmd
acSysCmdObjectState. It produced the same result, however.

 

[Guest]

Thanks Fred. I tried your suggestions.
Now the report prompts me w/ the form, then the query prompts me again.
What do I change so that I'm not prompted a second time w/ the query for the
results to go to the report?
--
Thanks, Karen

I followed a recommendation for achieving the above-mentioned project by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I wrote a
report that prompts the user to enter the parameter in a form, the form sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the form
prompt pops up, I make the selection in the form prompt, see the query
pulling up the correct data but continue to get "compile error: variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions. I get
the same result when I try to run the Sales by Category report that is given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to achieve
what I want? All assistance is prematurely appreciated.

I have no idea what the directions given were, nor, of course, what
you have or haven't done to implement them.

Here is my method, which you are welcome to adapt and use.

It uses a combo box to select a Company, as well as 2 controls to
enter date parameters. Adapt as needed.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also 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'.

In the Query, (the Report's Record Source) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

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 selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

To display the date parameters in the report, add an unbound text
control to the Report Header.
Set it's control source to:
="For sales between " & [forms!Paramform!StartDate] & " and " &
[forms!ParamForm!EndDate]

The text, i.e. forms!Paramform!StartDate & forms!ParamForm!EndDate,
must be identical to the text in the query. Include the brackets as
shown.

 

[Bob Quintal]

I did this Bob. The report now takes me to the form, I make a
selection, then it takes me to the query w/ the results. It
never pulls the information into report display.

The query opens up?

Please copy and paste all the code from the form and the open event
of the Report,. as well as the SQL of the query itself.

 

[Guest]

I did this Bob. The report now takes me to the form, I make a selection,
then it takes me to the query w/ the results. It never pulls the information
into report display.

 

[Guest]

I'll reattempt this; I believe I missed a step w/ creation of the Dialog
form. Your suggestion works perfectly in the sample database so I must've
messed up somewhere in my own project. Thanks for your patience and
assistance thus far. I'll reply afterwards.

 

[Guest]

I've decided to go w/ Fred's recommendation above; it's clean and short and
seems to work easier.
But now the report prompts me w/ the form, then the query prompts me again.
What can I do so that I'm not prompted a second time w/ the query for the
results to go to the report?

 

[fredg]

Thanks Fred. I tried your suggestions.
Now the report prompts me w/ the form, then the query prompts me again.
What do I change so that I'm not prompted a second time w/ the query for the
results to go to the report?

Did you code it exactly as I wrote it (changing the form and field
names is OK), or do you still have some of your old code perhaps
conflicting with what I gave you.

It would appear your form is closing before the report is run.
The form MUST remain open until the Report's Close event closes it.

 

[Guest]

Thanks for your wonderful assistance, Fred. It works.

Since I was querying on a single variable field, I omitted the command
button and put in an After Update event in the combo box. The language there
was this:

Private Sub cboBureau_AfterUpdate()
DoCmd.OpenQuery "Union Query - Bureau", acViewNormal, acEdit
Docmd.Close acForm, "Bureau Selector"
End Sub

What language might I use in the form's After Update event in the combo box
if I get rid of the command button?

 

[fredg]

Thanks for your wonderful assistance, Fred. It works.

Since I was querying on a single variable field, I omitted the command
button and put in an After Update event in the combo box. The language there
was this:

Private Sub cboBureau_AfterUpdate()
DoCmd.OpenQuery "Union Query - Bureau", acViewNormal, acEdit
Docmd.Close acForm, "Bureau Selector"
End Sub

What language might I use in the form's After Update event in the combo box
if I get rid of the command button?

Don't use the Combo AfterUpdate event.
When you open the ParamForm from the Report's Open event, it' opened
in Dialog mode. All code processing stops .... until you click on that
command button.
Also, since the Report uses the "Union Query - Bureau" query as it's
record source (it does, doesn't it?), you do NOT need to actually run
the query. So.. remove all of that Combvo Box AfterUpdate event code.

When you open the Report, the report will open the form. Enter
whatever parameter values you need in the form, and click the Command
button. The form will become not visible (but it is still open). The
report will display (but you do not see the query). When you close the
report it will also close the form.

 

[Very Basic User]

Hello Fred,

I was able to use your message to fix a problem. I have a form with a (Start
Date) and (End Date) text box that drives criteria to a query for a report.
My only question is how to control if left blank. In other criteria oprtions
I could put (& "*") to get at a blank entry. I tried to enter a default date
of 1/1/1800 in the start date and 1/1/3000 in the default for end date, but
that didn't seem to work.
--
Thank you for your time!
John

I followed a recommendation for achieving the above-mentioned project by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I wrote a
report that prompts the user to enter the parameter in a form, the form sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the form
prompt pops up, I make the selection in the form prompt, see the query
pulling up the correct data but continue to get "compile error: variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions. I get
the same result when I try to run the Sales by Category report that is given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to achieve
what I want? All assistance is prematurely appreciated.

I have no idea what the directions given were, nor, of course, what
you have or haven't done to implement them.

Here is my method, which you are welcome to adapt and use.

It uses a combo box to select a Company, as well as 2 controls to
enter date parameters. Adapt as needed.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also 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'.

In the Query, (the Report's Record Source) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

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 selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

To display the date parameters in the report, add an unbound text
control to the Report Header.
Set it's control source to:
="For sales between " & [forms!Paramform!StartDate] & " and " &
[forms!ParamForm!EndDate]

The text, i.e. forms!Paramform!StartDate & forms!ParamForm!EndDate,
must be identical to the text in the query. Include the brackets as
shown.

 

[Douglas J. Steele]

WHERE (SomeDate >= [Start Date]) OR ([Start Date] IS NULL)
AND (SomeDate <= [End Date]) OR ([End Date] IS NULL)

Another alternative is

WHERE SomeDate BETWEEN Nz([Start Date], #1/1/100#) AND Nz([End Date],
#12/31/9999#)

The difference, though, is that the first will return rows that have Null
values for SomeDate if no dates are provided, while the second will only
returns rows where SomeDate has a value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Fred,

I was able to use your message to fix a problem. I have a form with a
(Start
Date) and (End Date) text box that drives criteria to a query for a
report.
My only question is how to control if left blank. In other criteria
oprtions
I could put (& "*") to get at a blank entry. I tried to enter a default
date
of 1/1/1800 in the start date and 1/1/3000 in the default for end date,
but
that didn't seem to work.
--
Thank you for your time!
John

I followed a recommendation for achieving the above-mentioned project
by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I wrote
a
report that prompts the user to enter the parameter in a form, the form
sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the
form
prompt pops up, I make the selection in the form prompt, see the query
pulling up the correct data but continue to get "compile error:
variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions. I
get
the same result when I try to run the Sales by Category report that is
given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to
achieve
what I want? All assistance is prematurely appreciated.

I have no idea what the directions given were, nor, of course, what
you have or haven't done to implement them.

Here is my method, which you are welcome to adapt and use.

It uses a combo box to select a Company, as well as 2 controls to
enter date parameters. Adapt as needed.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also 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'.

In the Query, (the Report's Record Source) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

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 selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

To display the date parameters in the report, add an unbound text
control to the Report Header.
Set it's control source to:
="For sales between " & [forms!Paramform!StartDate] & " and " &
[forms!ParamForm!EndDate]

The text, i.e. forms!Paramform!StartDate & forms!ParamForm!EndDate,
must be identical to the text in the query. Include the brackets as
shown.

 

[Very Basic User]

Thanks for your help! I'm not quite sure what to type literal and what to
review to my query... If the statement below gets me results when I add a
date for each field, how would I type if with your second recomendation. Each
record should have a date entered as part of a required entry.

Between [forms]![ReportOptions]![Start Date] And
[forms]![ReportOptions]![End Date]
--
Thank you for your time!
John

WHERE (SomeDate >= [Start Date]) OR ([Start Date] IS NULL)
AND (SomeDate <= [End Date]) OR ([End Date] IS NULL)

Another alternative is

WHERE SomeDate BETWEEN Nz([Start Date], #1/1/100#) AND Nz([End Date],
#12/31/9999#)

The difference, though, is that the first will return rows that have Null
values for SomeDate if no dates are provided, while the second will only
returns rows where SomeDate has a value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Fred,

I was able to use your message to fix a problem. I have a form with a
(Start
Date) and (End Date) text box that drives criteria to a query for a
report.
My only question is how to control if left blank. In other criteria
oprtions
I could put (& "*") to get at a blank entry. I tried to enter a default
date
of 1/1/1800 in the start date and 1/1/3000 in the default for end date,
but
that didn't seem to work.
--
Thank you for your time!
John

On Fri, 17 Aug 2007 06:26:01 -0700, Karen wrote:

I followed a recommendation for achieving the above-mentioned project
by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I wrote
a
report that prompts the user to enter the parameter in a form, the form
sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the
form
prompt pops up, I make the selection in the form prompt, see the query
pulling up the correct data but continue to get "compile error:
variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions. I
get
the same result when I try to run the Sales by Category report that is
given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to
achieve
what I want? All assistance is prematurely appreciated.

I have no idea what the directions given were, nor, of course, what
you have or haven't done to implement them.

Here is my method, which you are welcome to adapt and use.

It uses a combo box to select a Company, as well as 2 controls to
enter date parameters. Adapt as needed.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also 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'.

In the Query, (the Report's Record Source) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

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 selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

To display the date parameters in the report, add an unbound text
control to the Report Header.
Set it's control source to:
="For sales between " & [forms!Paramform!StartDate] & " and " &
[forms!ParamForm!EndDate]

The text, i.e. forms!Paramform!StartDate & forms!ParamForm!EndDate,
must be identical to the text in the query. Include the brackets as
shown.

 

[Douglas J. Steele]

Between Nz([forms]![ReportOptions]![Start Date], #1/1/100#) And
Nz([forms]![ReportOptions]![End Date], #12/31/9999#)

or

(>= [forms]![ReportOptions]![Start Date] OR [forms]![ReportOptions]![Start
Date] IS NULL)
And (<= [forms]![ReportOptions]![End Date] OR [forms]![ReportOptions]![End
Date] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your help! I'm not quite sure what to type literal and what to
review to my query... If the statement below gets me results when I add a
date for each field, how would I type if with your second recomendation.
Each
record should have a date entered as part of a required entry.

Between [forms]![ReportOptions]![Start Date] And
[forms]![ReportOptions]![End Date]
--
Thank you for your time!
John

WHERE (SomeDate >= [Start Date]) OR ([Start Date] IS NULL)
AND (SomeDate <= [End Date]) OR ([End Date] IS NULL)

Another alternative is

WHERE SomeDate BETWEEN Nz([Start Date], #1/1/100#) AND Nz([End Date],
#12/31/9999#)

The difference, though, is that the first will return rows that have Null
values for SomeDate if no dates are provided, while the second will only
returns rows where SomeDate has a value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message

Hello Fred,

I was able to use your message to fix a problem. I have a form with a
(Start
Date) and (End Date) text box that drives criteria to a query for a
report.
My only question is how to control if left blank. In other criteria
oprtions
I could put (& "*") to get at a blank entry. I tried to enter a default
date
of 1/1/1800 in the start date and 1/1/3000 in the default for end date,
but
that didn't seem to work.
--
Thank you for your time!
John


:

On Fri, 17 Aug 2007 06:26:01 -0700, Karen wrote:

I followed a recommendation for achieving the above-mentioned
project
by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I
wrote
a
report that prompts the user to enter the parameter in a form, the
form
sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the
form
prompt pops up, I make the selection in the form prompt, see the
query
pulling up the correct data but continue to get "compile error:
variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of
the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions.
I
get
the same result when I try to run the Sales by Category report that
is
given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to
achieve
what I want? All assistance is prematurely appreciated.

I have no idea what the directions given were, nor, of course, what
you have or haven't done to implement them.

Here is my method, which you are welcome to adapt and use.

It uses a combo box to select a Company, as well as 2 controls to
enter date parameters. Adapt as needed.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also 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'.

In the Query, (the Report's Record Source) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

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 selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

To display the date parameters in the report, add an unbound text
control to the Report Header.
Set it's control source to:
="For sales between " & [forms!Paramform!StartDate] & " and " &
[forms!ParamForm!EndDate]

The text, i.e. forms!Paramform!StartDate & forms!ParamForm!EndDate,
must be identical to the text in the query. Include the brackets as
shown.

 

[Very Basic User]

Thank you so much! This resolved the problom and works perfectly! I
appreciate your time!
--
Thank you for your time!
John

Between Nz([forms]![ReportOptions]![Start Date], #1/1/100#) And
Nz([forms]![ReportOptions]![End Date], #12/31/9999#)

or

(>= [forms]![ReportOptions]![Start Date] OR [forms]![ReportOptions]![Start
Date] IS NULL)
And (<= [forms]![ReportOptions]![End Date] OR [forms]![ReportOptions]![End
Date] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your help! I'm not quite sure what to type literal and what to
review to my query... If the statement below gets me results when I add a
date for each field, how would I type if with your second recomendation.
Each
record should have a date entered as part of a required entry.

Between [forms]![ReportOptions]![Start Date] And
[forms]![ReportOptions]![End Date]
--
Thank you for your time!
John

WHERE (SomeDate >= [Start Date]) OR ([Start Date] IS NULL)
AND (SomeDate <= [End Date]) OR ([End Date] IS NULL)

Another alternative is

WHERE SomeDate BETWEEN Nz([Start Date], #1/1/100#) AND Nz([End Date],
#12/31/9999#)

The difference, though, is that the first will return rows that have Null
values for SomeDate if no dates are provided, while the second will only
returns rows where SomeDate has a value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hello Fred,

I was able to use your message to fix a problem. I have a form with a
(Start
Date) and (End Date) text box that drives criteria to a query for a
report.
My only question is how to control if left blank. In other criteria
oprtions
I could put (& "*") to get at a blank entry. I tried to enter a default
date
of 1/1/1800 in the start date and 1/1/3000 in the default for end date,
but
that didn't seem to work.
--
Thank you for your time!
John


:

On Fri, 17 Aug 2007 06:26:01 -0700, Karen wrote:

I followed a recommendation for achieving the above-mentioned
project
by
following directions given at
http://office.microsoft.com/en-us/access/Ha011170771033.aspx. I
wrote
a
report that prompts the user to enter the parameter in a form, the
form
sends
the selection to the query and displays the results in the report.

Seemingly, I'm able to achieve a bit of it. I open the report, the
form
prompt pops up, I make the selection in the form prompt, see the
query
pulling up the correct data but continue to get "compile error:
variable not
defined" in the module code that I entered exactly as shown in the
instructions under the Adding a supporting code module section of
the
instructions. The system highlights "acCurViewDesign."

I even downloaded the sample database given within the instructions.
I
get
the same result when I try to run the Sales by Category report that
is
given
as an example. FYI - I'm using Access 2000.

Pls advise - what is wrong with the coding and how do I fix it to
achieve
what I want? All assistance is prematurely appreciated.

I have no idea what the directions given were, nor, of course, what
you have or haven't done to implement them.

Here is my method, which you are welcome to adapt and use.

It uses a combo box to select a Company, as well as 2 controls to
enter date parameters. Adapt as needed.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Also 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'.

In the Query, (the Report's Record Source) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

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 selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

To display the date parameters in the report, add an unbound text
control to the Report Header.
Set it's control source to:
="For sales between " & [forms!Paramform!StartDate] & " and " &
[forms!ParamForm!EndDate]

The text, i.e. forms!Paramform!StartDate & forms!ParamForm!EndDate,
must be identical to the text in the query. Include the brackets as
shown.