Multiple date parameters

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!


0
Utf
5/2/2007 8:43:02 PM
access.forms 6864 articles. 2 followers. Follow

5 Replies
1071 Views

Similar Articles

[PageSpeed] 7

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

"tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message 
news:A1E289FD-8294-4075-AB24-D4F70A386BAC@microsoft.com...
>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!
>
> 


0
Jeff
5/2/2007 11:20:25 PM
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?

"Jeff Boyce" wrote:

> 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
> 
> "tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message 
> news:A1E289FD-8294-4075-AB24-D4F70A386BAC@microsoft.com...
> >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!
> >
> > 
> 
> 
> 
0
Utf
5/4/2007 8:20:01 PM
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

"tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message 
news:95D1C681-8E7E-48DA-BCB3-AC54DA9B94E9@microsoft.com...
> 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?
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>> "tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message
>> news:A1E289FD-8294-4075-AB24-D4F70A386BAC@microsoft.com...
>> >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!
>> >
>> >
>>
>>
>> 


0
Jeff
5/4/2007 11:20:01 PM
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.

"Jeff Boyce" wrote:

> 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
> 
> "tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message 
> news:95D1C681-8E7E-48DA-BCB3-AC54DA9B94E9@microsoft.com...
> > 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?
> >
> > "Jeff Boyce" wrote:
> >
> >> 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
> >>
> >> "tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message
> >> news:A1E289FD-8294-4075-AB24-D4F70A386BAC@microsoft.com...
> >> >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!
> >> >
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Utf
5/7/2007 4:20:00 PM
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




"tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message 
news:A61B8999-DA74-4D1A-A645-E5EE2C2D4C83@microsoft.com...
>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.
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>> "tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message
>> news:95D1C681-8E7E-48DA-BCB3-AC54DA9B94E9@microsoft.com...
>> > 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?
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> 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
>> >>
>> >> "tarheelkate" <tarheelkate@discussions.microsoft.com> wrote in message
>> >> news:A1E289FD-8294-4075-AB24-D4F70A386BAC@microsoft.com...
>> >> >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!
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>> 


0
Jeff
5/7/2007 5:31:37 PM
Reply:

Similar Artilces: