Using A form to input criteria

[Bob]

Hi I have a form named ParamForm that has two unbound text boxes name
SartDate and EndDate.

I would like to have my query open this form and to be able to input the
start date and end date bofore running the query and the attacthed report.

This is the code that I have in the critera field of the query but all that
it does is return a paramater message.. I would also like to replace Between
with <=.

Between "Forms]![ParamForm]![StartDate]" And [Forms]![ParamForm]![End Date]

What am I doing wrong?

Thanks Bob

 

[Douglas J. Steele]

Sorry, you can't have the query open the form. The form must be open before
the query runs.

What you've got has incorrect quotes in it. It should be:

Between [Forms]![ParamForm]![StartDate] And [Forms]![ParamForm]![End Date]

Not sure why you think you need to replace Between with <=. Between is
inclusive. In other words,

MyDateField Between [Forms]![ParamForm]![StartDate] And
[Forms]![ParamForm]![End Date]

is identical to

MyDateField >= [Forms]![ParamForm]![StartDate] And MyDateField <=
[Forms]![ParamForm]![End Date]

Now, perhaps you've tried plugging in literal dates, and you're finding
you're not getting records from your table where MyDateField is equal to the
EndDate. That's likely because MyDateField contains not just the date, but
the time as well. If that's the case, use:

Between [Forms]![ParamForm]![StartDate] And DateAdd("d", 1,
[Forms]![ParamForm]![End Date])

 

[Bob]

Thanks Doug now how do I open the form from the report...

Bob

Sorry, you can't have the query open the form. The form must be open before
the query runs.

What you've got has incorrect quotes in it. It should be:

Between [Forms]![ParamForm]![StartDate] And [Forms]![ParamForm]![End Date]

Not sure why you think you need to replace Between with <=. Between is
inclusive. In other words,

MyDateField Between [Forms]![ParamForm]![StartDate] And
[Forms]![ParamForm]![End Date]

is identical to

MyDateField >= [Forms]![ParamForm]![StartDate] And MyDateField <=
[Forms]![ParamForm]![End Date]

Now, perhaps you've tried plugging in literal dates, and you're finding
you're not getting records from your table where MyDateField is equal to the
EndDate. That's likely because MyDateField contains not just the date, but
the time as well. If that's the case, use:

Between [Forms]![ParamForm]![StartDate] And DateAdd("d", 1,
[Forms]![ParamForm]![End Date])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi I have a form named ParamForm that has two unbound text boxes name
SartDate and EndDate.

I would like to have my query open this form and to be able to input the
start date and end date bofore running the query and the attacthed report.

This is the code that I have in the critera field of the query but all
that
it does is return a paramater message.. I would also like to replace
Between
with <=.

Between "Forms]![ParamForm]![StartDate]" And [Forms]![ParamForm]![End
Date]

What am I doing wrong?

Thanks Bob

 

[fredg]

Thanks Doug now how do I open the form from the report...

Bob

Sorry, you can't have the query open the form. The form must be open before
the query runs.

What you've got has incorrect quotes in it. It should be:

Between [Forms]![ParamForm]![StartDate] And [Forms]![ParamForm]![End Date]

Not sure why you think you need to replace Between with <=. Between is
inclusive. In other words,

MyDateField Between [Forms]![ParamForm]![StartDate] And
[Forms]![ParamForm]![End Date]

is identical to

MyDateField >= [Forms]![ParamForm]![StartDate] And MyDateField <=
[Forms]![ParamForm]![End Date]

Now, perhaps you've tried plugging in literal dates, and you're finding
you're not getting records from your table where MyDateField is equal to the
EndDate. That's likely because MyDateField contains not just the date, but
the time as well. If that's the case, use:

Between [Forms]![ParamForm]![StartDate] And DateAdd("d", 1,
[Forms]![ParamForm]![End Date])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi I have a form named ParamForm that has two unbound text boxes name
SartDate and EndDate.

I would like to have my query open this form and to be able to input the
start date and end date bofore running the query and the attacthed report.

This is the code that I have in the critera field of the query but all
that
it does is return a paramater message.. I would also like to replace
Between
with <=.

Between "Forms]![ParamForm]![StartDate]" And [Forms]![ParamForm]![End
Date]

What am I doing wrong?

Thanks Bob

Code the Report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

Have a command button on the form:
Me.Visible = False

Open the Report. The Report will open the form.
Enter the dates.
When you click on the form's command button, the form will hide and
the report will run. When you close the report, it will also close the
form.

 

[Bob]

Hi Fred.. When I posted in the other forum no one had answered here..

I did as you suggested and received an error when opening the form. The
error was Object DoCmd can not be found.. I copied and pasted the code that
you gave me..

Thanks for help fred.

Bob

Thanks Doug now how do I open the form from the report...

Bob

Sorry, you can't have the query open the form. The form must be open before
the query runs.

What you've got has incorrect quotes in it. It should be:

Between [Forms]![ParamForm]![StartDate] And [Forms]![ParamForm]![End Date]

Not sure why you think you need to replace Between with <=. Between is
inclusive. In other words,

MyDateField Between [Forms]![ParamForm]![StartDate] And
[Forms]![ParamForm]![End Date]

is identical to

MyDateField >= [Forms]![ParamForm]![StartDate] And MyDateField <=
[Forms]![ParamForm]![End Date]

Now, perhaps you've tried plugging in literal dates, and you're finding
you're not getting records from your table where MyDateField is equal to the
EndDate. That's likely because MyDateField contains not just the date, but
the time as well. If that's the case, use:

Between [Forms]![ParamForm]![StartDate] And DateAdd("d", 1,
[Forms]![ParamForm]![End Date])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi I have a form named ParamForm that has two unbound text boxes name
SartDate and EndDate.

I would like to have my query open this form and to be able to input the
start date and end date bofore running the query and the attacthed report.

This is the code that I have in the critera field of the query but all
that
it does is return a paramater message.. I would also like to replace
Between
with <=.

Between "Forms]![ParamForm]![StartDate]" And [Forms]![ParamForm]![End
Date]

What am I doing wrong?

Thanks Bob

Code the Report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

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

Have a command button on the form:
Me.Visible = False

Open the Report. The Report will open the form.
Enter the dates.
When you click on the form's command button, the form will hide and
the report will run. When you close the report, it will also close the
form.