Filtering on a series of Dates via a combo box

[AJOLSON]

I am trying to apply a filter to a series of dates.

An Example is:
I want to filter on all records with dates that fall between Jul, 1,2008 and
July, 7, 2008 (a seven day period) I am using a combo box to try and do this.


Here are the two codes I have used:
Code #1
Me.Filter = "[ProductionDate] >= " & Format(Me.ComboFilterDate,
"\#yyyy\-mm\-dd\#")
Me.Filter = "[ ProductionDate]- 6 <= " & Format(Me. ComboFilterDate,
"\#yyyy\-mm\-dd\#")
Me.FilterOn = True

Code #2
I did try this too but to no avail
Me.Filter = "[ProductionDate] >= and [ProductionDate]-6<= " &
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#")

Neither code works
In Code #1 example of what is happening is the first line is working great
I get all Production dates that are the date selected and higher, However,
when the second filter is applied it wipes out the first one and returns all
records that are 7 days after the selected date to the first date of
production. Which is undesirable.

In Code#2 all I get is a syntax error
(missing Operator ) in query expression ‘[ProductionDate}>=and
[ProductionDate]-7<= #2008-07-25#’

I figure the issue is placing an “and†qualifier in there somewhere but
can’t figure out where to put it.
So any help would be greatly appreciated. Thanks
Andy

 

[Douglas J. Steele]

Me.Filter = "[ProductionDate] BETWEEN " & _
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(DateAdd("d", 7, Me.ComboFilterDate), "\#yyyy\-mm\-dd\#")
Me.FilterOn = True

 

[AJOLSON]

That did the trick.

Now Query used in this form is filtering correclty but there is another
problem.
The results of this filter is not pass along to other queries that are baced
on the the filtered query used on the form. How do I get all 'Sub"Queries to
relfect the applied filter?


Thanks All
Andy

Me.Filter = "[ProductionDate] BETWEEN " & _
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(DateAdd("d", 7, Me.ComboFilterDate), "\#yyyy\-mm\-dd\#")
Me.FilterOn = True


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to apply a filter to a series of dates.

An Example is:
I want to filter on all records with dates that fall between Jul, 1,2008
and
July, 7, 2008 (a seven day period) I am using a combo box to try and do
this.


Here are the two codes I have used:
Code #1
Me.Filter = "[ProductionDate] >= " & Format(Me.ComboFilterDate,
"\#yyyy\-mm\-dd\#")
Me.Filter = "[ ProductionDate]- 6 <= " & Format(Me. ComboFilterDate,
"\#yyyy\-mm\-dd\#")
Me.FilterOn = True

Code #2
I did try this too but to no avail
Me.Filter = "[ProductionDate] >= and [ProductionDate]-6<= " &
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#")

Neither code works
In Code #1 example of what is happening is the first line is working
great
I get all Production dates that are the date selected and higher,
However,
when the second filter is applied it wipes out the first one and returns
all
records that are 7 days after the selected date to the first date of
production. Which is undesirable.

In Code#2 all I get is a syntax error
(missing Operator ) in query expression '[ProductionDate}>=and
[ProductionDate]-7<= #2008-07-25#'

I figure the issue is placing an "and" qualifier in there somewhere but
can't figure out where to put it.
So any help would be greatly appreciated. Thanks
Andy

 

[Douglas J. Steele]

You'll have to make the query point to the control on the form rather than
use a filter.

Use

Forms![NameOfForm]![NameOfControl]

as the criteria. If you want everything returned if there's nothing in the
control use

Forms![NameOfForm]![NameOfControl] Or (Forms![NameOfForm]![NameOfControl]
IS NULL)

The form must be open in order to use the query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That did the trick.

Now Query used in this form is filtering correclty but there is another
problem.
The results of this filter is not pass along to other queries that are
baced
on the the filtered query used on the form. How do I get all 'Sub"Queries
to
relfect the applied filter?


Thanks All
Andy

Me.Filter = "[ProductionDate] BETWEEN " & _
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(DateAdd("d", 7, Me.ComboFilterDate), "\#yyyy\-mm\-dd\#")
Me.FilterOn = True


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to apply a filter to a series of dates.

An Example is:
I want to filter on all records with dates that fall between Jul,
1,2008
and
July, 7, 2008 (a seven day period) I am using a combo box to try and do
this.


Here are the two codes I have used:
Code #1
Me.Filter = "[ProductionDate] >= " & Format(Me.ComboFilterDate,
"\#yyyy\-mm\-dd\#")
Me.Filter = "[ ProductionDate]- 6 <= " & Format(Me. ComboFilterDate,
"\#yyyy\-mm\-dd\#")
Me.FilterOn = True

Code #2
I did try this too but to no avail
Me.Filter = "[ProductionDate] >= and [ProductionDate]-6<= " &
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#")

Neither code works
In Code #1 example of what is happening is the first line is working
great
I get all Production dates that are the date selected and higher,
However,
when the second filter is applied it wipes out the first one and
returns
all
records that are 7 days after the selected date to the first date of
production. Which is undesirable.

In Code#2 all I get is a syntax error
(missing Operator ) in query expression '[ProductionDate}>=and
[ProductionDate]-7<= #2008-07-25#'

I figure the issue is placing an "and" qualifier in there somewhere but
can't figure out where to put it.
So any help would be greatly appreciated. Thanks
Andy

 

[Bob Quintal]

That did the trick.

Now Query used in this form is filtering correclty but there is
another problem.
The results of this filter is not pass along to other queries that
are baced on the the filtered query used on the form. How do I
get all 'Sub"Queries to relfect the applied filter?


Thanks All
Andy

You are mixing forms and queries Andy, and like apples and oranges,
they don't compute.

The filter applied to a form affects only that form. Any subqueries
use a new copy of the query in the form, not the same query, so you
cannot filter it. You would need to open the sub query in a form and
apply the filter there too.

Another way is to put parameters in the query and use those to
filter. But you cannot use a combobox there.

We'd need to know more about WHAT you're trying to do, in order to
tell you HOW to do it.

Q

Me.Filter = "[ProductionDate] BETWEEN " & _
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(DateAdd("d", 7, Me.ComboFilterDate), "\#yyyy\-mm\-dd\#")
Me.FilterOn = True


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to apply a filter to a series of dates.

An Example is:
I want to filter on all records with dates that fall between
Jul, 1,2008 and
July, 7, 2008 (a seven day period) I am using a combo box to
try and do this.


Here are the two codes I have used:
Code #1
Me.Filter = "[ProductionDate] >= " & Format(Me.ComboFilterDate,
"\#yyyy\-mm\-dd\#")
Me.Filter = "[ ProductionDate]- 6 <= " & Format(Me.
ComboFilterDate, "\#yyyy\-mm\-dd\#")
Me.FilterOn = True

Code #2
I did try this too but to no avail
Me.Filter = "[ProductionDate] >= and [ProductionDate]-6<= " &
Format(Me.ComboFilterDate, "\#yyyy\-mm\-dd\#")

Neither code works
In Code #1 example of what is happening is the first line is
working great
I get all Production dates that are the date selected and
higher, However,
when the second filter is applied it wipes out the first one
and returns all
records that are 7 days after the selected date to the first
date of production. Which is undesirable.

In Code#2 all I get is a syntax error
(missing Operator ) in query expression '[ProductionDate}>=and
[ProductionDate]-7<= #2008-07-25#'

I figure the issue is placing an "and" qualifier in there
somewhere but can't figure out where to put it.
So any help would be greatly appreciated. Thanks
Andy