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