Multiple date parameters

[Guest]

I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots.

My main form is built off of MonthlyAll. It's main purpose is to provide a
list of cities in a single form view, so that I can scroll through the cities
and view the corresponding data.

The main form contains two subforms: MonthlyShows and MonthlySpots. The
subforms are linked to the main form by city and display the respective shows
and spots by date.

My question seems simple enough: Upon opening the form, I would like to type
in a single date range ("Between [] And []"); and view only those shows and
spots in each city.

When I add "Between [] And []" criteria to MonthlyShows and MonthlySpots, it
prompts me for the date range twice. Then, once the main form is opened, if
I try to scoll to the next city (ie the next record), it prompts me for the
date criteria two more times, once for each subform.

How can I simplify this so that I only enter the date criteria one time when
I open the form? Is this possible?

(I tried using the solution in the thread "Multiple Date Parameters...
Simplify" but I could not get it to work.)

My only other option is setting the date criteria on both MonthlyShows and
MonthlySpots to 1/1/07 to 1/31/07 and naming the main form "January". Then
creating a form for each month thereafter. But that seems like a lot of
unnecessary work.

Thanks for your help!

 

[Jeff Boyce]

Add two textbox controls to your form. Have one be for "FromDate" and the
other "ToDate".

Modify the queries underlying your two subforms. In the Selection Criterion
"cell" under the date field, use:

Between Forms!YourMainForm!FromDate And Forms!YourMainForm!ToDate

(your actual names/syntax will vary).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for all your help. I'm still relatively new to this, so I'm requesting
come clarification. (And probably asking some stupid questions along the
way)...

When you say add two textbox controls, do I need to add them to my originial
MontlyAll table... then to my MonthlyAll query... and then they'll show up in
my field list and I can add them to my form?

Add two textbox controls to your form. Have one be for "FromDate" and the
other "ToDate".

Modify the queries underlying your two subforms. In the Selection Criterion
"cell" under the date field, use:

Between Forms!YourMainForm!FromDate And Forms!YourMainForm!ToDate

(your actual names/syntax will vary).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots.

My main form is built off of MonthlyAll. It's main purpose is to provide
a
list of cities in a single form view, so that I can scroll through the
cities
and view the corresponding data.

The main form contains two subforms: MonthlyShows and MonthlySpots. The
subforms are linked to the main form by city and display the respective
shows
and spots by date.

My question seems simple enough: Upon opening the form, I would like to
type
in a single date range ("Between [] And []"); and view only those shows
and
spots in each city.

When I add "Between [] And []" criteria to MonthlyShows and MonthlySpots,
it
prompts me for the date range twice. Then, once the main form is opened,
if
I try to scoll to the next city (ie the next record), it prompts me for
the
date criteria two more times, once for each subform.

How can I simplify this so that I only enter the date criteria one time
when
I open the form? Is this possible?

(I tried using the solution in the thread "Multiple Date Parameters...
Simplify" but I could not get it to work.)

My only other option is setting the date criteria on both MonthlyShows and
MonthlySpots to 1/1/07 to 1/31/07 and naming the main form "January".
Then
creating a form for each month thereafter. But that seems like a lot of
unnecessary work.

Thanks for your help!

 

[Jeff Boyce]

A bit of Access terminology (or jargon ... <g>). In Access, the things in
tables are fields (the SQL folks call them columns). The things in forms
(and reports) that show what's in the tables (and other stuff) are called
controls.

So my earlier suggestion was to open your form in design view and add the
two new controls, NEITHER of which connect to any table fields. They will
be temporary buckets into which you'll add data parameters.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for all your help. I'm still relatively new to this, so I'm
requesting
come clarification. (And probably asking some stupid questions along the
way)...

When you say add two textbox controls, do I need to add them to my
originial
MontlyAll table... then to my MonthlyAll query... and then they'll show up
in
my field list and I can add them to my form?

Add two textbox controls to your form. Have one be for "FromDate" and
the
other "ToDate".

Modify the queries underlying your two subforms. In the Selection
Criterion
"cell" under the date field, use:

Between Forms!YourMainForm!FromDate And Forms!YourMainForm!ToDate

(your actual names/syntax will vary).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots.

My main form is built off of MonthlyAll. It's main purpose is to
provide
a
list of cities in a single form view, so that I can scroll through the
cities
and view the corresponding data.

The main form contains two subforms: MonthlyShows and MonthlySpots.
The
subforms are linked to the main form by city and display the respective
shows
and spots by date.

My question seems simple enough: Upon opening the form, I would like to
type
in a single date range ("Between [] And []"); and view only those shows
and
spots in each city.

When I add "Between [] And []" criteria to MonthlyShows and
MonthlySpots,
it
prompts me for the date range twice. Then, once the main form is
opened,
if
I try to scoll to the next city (ie the next record), it prompts me for
the
date criteria two more times, once for each subform.

How can I simplify this so that I only enter the date criteria one time
when
I open the form? Is this possible?

(I tried using the solution in the thread "Multiple Date Parameters...
Simplify" but I could not get it to work.)

My only other option is setting the date criteria on both MonthlyShows
and
MonthlySpots to 1/1/07 to 1/31/07 and naming the main form "January".
Then
creating a form for each month thereafter. But that seems like a lot
of
unnecessary work.

Thanks for your help!

 

[Guest]

I hate that I'm a newbie and just don't seem to be getting this... I really
do appreciate your patience.

I tried adding two text controls (thanks for the jargon). But when I open
the MonthlyAll form, it does not prompt for dates. There is, in fact, no
data at all in the subforms. The two text controls show up as "#Name?".

When I open MonthlySpots and MonthlyShows independently, it prompts me
"Forms!MonthlyAll!FromDate", so it seems like I did at least one step
correctly.

Thanks again for your help.

A bit of Access terminology (or jargon ... <g>). In Access, the things in
tables are fields (the SQL folks call them columns). The things in forms
(and reports) that show what's in the tables (and other stuff) are called
controls.

So my earlier suggestion was to open your form in design view and add the
two new controls, NEITHER of which connect to any table fields. They will
be temporary buckets into which you'll add data parameters.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for all your help. I'm still relatively new to this, so I'm
requesting
come clarification. (And probably asking some stupid questions along the
way)...

When you say add two textbox controls, do I need to add them to my
originial
MontlyAll table... then to my MonthlyAll query... and then they'll show up
in
my field list and I can add them to my form?

Add two textbox controls to your form. Have one be for "FromDate" and
the
other "ToDate".

Modify the queries underlying your two subforms. In the Selection
Criterion
"cell" under the date field, use:

Between Forms!YourMainForm!FromDate And Forms!YourMainForm!ToDate

(your actual names/syntax will vary).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots.

My main form is built off of MonthlyAll. It's main purpose is to
provide
a
list of cities in a single form view, so that I can scroll through the
cities
and view the corresponding data.

The main form contains two subforms: MonthlyShows and MonthlySpots.
The
subforms are linked to the main form by city and display the respective
shows
and spots by date.

My question seems simple enough: Upon opening the form, I would like to
type
in a single date range ("Between [] And []"); and view only those shows
and
spots in each city.

When I add "Between [] And []" criteria to MonthlyShows and
MonthlySpots,
it
prompts me for the date range twice. Then, once the main form is
opened,
if
I try to scoll to the next city (ie the next record), it prompts me for
the
date criteria two more times, once for each subform.

How can I simplify this so that I only enter the date criteria one time
when
I open the form? Is this possible?

(I tried using the solution in the thread "Multiple Date Parameters...
Simplify" but I could not get it to work.)

My only other option is setting the date criteria on both MonthlyShows
and
MonthlySpots to 1/1/07 to 1/31/07 and naming the main form "January".
Then
creating a form for each month thereafter. But that seems like a lot
of
unnecessary work.

Thanks for your help!

 

[Jeff Boyce]

When "#Name" shows, Access is telling you that something is spelled
differently that it expects. Or that the control is bound to a table field
that it doesn't recognize. Click on one of the new controls. Select
properties. If there's anything in the Control Source property, delete
it... the two controls need to be unbound.

Next, I hadn't realized that you wanted the FORM to prompt you for a date.
The approach I offered requires you to enter dates in the two fields (if you
wish to), before running the query.

The queries will not "find" the date values unless the form is open and
dates are in the From and To controls.

And if I left it out last time, add a command button to your form that runs
the report (based on the query, based on the form's From/To values).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I hate that I'm a newbie and just don't seem to be getting this... I really
do appreciate your patience.

I tried adding two text controls (thanks for the jargon). But when I open
the MonthlyAll form, it does not prompt for dates. There is, in fact, no
data at all in the subforms. The two text controls show up as "#Name?".

When I open MonthlySpots and MonthlyShows independently, it prompts me
"Forms!MonthlyAll!FromDate", so it seems like I did at least one step
correctly.

Thanks again for your help.

A bit of Access terminology (or jargon ... <g>). In Access, the things
in
tables are fields (the SQL folks call them columns). The things in forms
(and reports) that show what's in the tables (and other stuff) are called
controls.

So my earlier suggestion was to open your form in design view and add the
two new controls, NEITHER of which connect to any table fields. They
will
be temporary buckets into which you'll add data parameters.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for all your help. I'm still relatively new to this, so I'm
requesting
come clarification. (And probably asking some stupid questions along
the
way)...

When you say add two textbox controls, do I need to add them to my
originial
MontlyAll table... then to my MonthlyAll query... and then they'll show
up
in
my field list and I can add them to my form?

:

Add two textbox controls to your form. Have one be for "FromDate" and
the
other "ToDate".

Modify the queries underlying your two subforms. In the Selection
Criterion
"cell" under the date field, use:

Between Forms!YourMainForm!FromDate And Forms!YourMainForm!ToDate

(your actual names/syntax will vary).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots.

My main form is built off of MonthlyAll. It's main purpose is to
provide
a
list of cities in a single form view, so that I can scroll through
the
cities
and view the corresponding data.

The main form contains two subforms: MonthlyShows and MonthlySpots.
The
subforms are linked to the main form by city and display the
respective
shows
and spots by date.

My question seems simple enough: Upon opening the form, I would like
to
type
in a single date range ("Between [] And []"); and view only those
shows
and
spots in each city.

When I add "Between [] And []" criteria to MonthlyShows and
MonthlySpots,
it
prompts me for the date range twice. Then, once the main form is
opened,
if
I try to scoll to the next city (ie the next record), it prompts me
for
the
date criteria two more times, once for each subform.

How can I simplify this so that I only enter the date criteria one
time
when
I open the form? Is this possible?

(I tried using the solution in the thread "Multiple Date
Parameters...
Simplify" but I could not get it to work.)

My only other option is setting the date criteria on both
MonthlyShows
and
MonthlySpots to 1/1/07 to 1/31/07 and naming the main form
"January".
Then
creating a form for each month thereafter. But that seems like a
lot
of
unnecessary work.

Thanks for your help!