Filter for THIS WEEK only

[Kathy Webster]

I have a table of dates and events. I would like to know how to make a query
that filters to display on any given day, only records whose events are
scheduled for the current week only.

For example, if they click this action button any time during the week, the
filter will return records with events scheduled for the prior Sunday
through the following Saturday.

 

[Allen Browne]

In the Click event of the button on your form, you can set the form's filter
so the SaleDate field contains only this week's records like this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the same
strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

 

[Guest]

Allen did it in code. Mine is very similiar but works as the criteria in a
query.

Between (Date()-Weekday(Date())+1) And (Date()+Weekday(Date())-1)

 

[Dale Fye]

Kathy,

Try a sql statement that looks something like:

Select YourTable.*
WHERE yourTable.EventDate Between DateAdd("d", -Weekday(Date), Date()) + 1
AND DateAdd("d", 8-Weekday(date), date())

The weekday function returns the numeric date of the day of the week,
starting with Sunday (1) and ending with Saturday (7).
You did not indicate whether your event dates contain times or not, but the
way this is formatted, it should not matter.

HTH
Dale

 

[Kathy Webster]

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head ,since
I work mostly in macros and filters and queries to accomplish my tasks, so I
am going to try to work with Jerry's reply, which is at my level! But your
code may enlighten me as to another issue I have. Does "dirty" mean the
record has been edited and not yet saved? Because I have situations where
upon clicking a command button on a form, the first macro step is to save
the record. But if the user happens to be a read-only user, this blows up.
Could I make a macro condition that says if the record is dirty, then save?
(If not, which would be the case if the user was a read-only user, I could
have the macro continue from there, skipping the save step) How would my
conditional phrase look if I did that?

In the Click event of the button on your form, you can set the form's
filter so the SaleDate field contains only this week's records like this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the same
strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table of dates and events. I would like to know how to make a
query that filters to display on any given day, only records whose events
are scheduled for the current week only.

For example, if they click this action button any time during the week,
the filter will return records with events scheduled for the prior Sunday
through the following Saturday.

 

[Kathy Webster]

Jerry,
I love your simple solution.Thank you. I want to use it, but I need some
additional help. Where I am actually putting this is in a macro that's
action is APPLY FILTER, and the formula needs to go in the WHERE CONDITION
argument.
I modified it to this, but I am getting 0 resulting records, when I should
have 5:

=([Date] Between (Date()-Weekday(Date())+1) And (Date()+Weekday(Date())-1))

Thanks,
Kathy

 

[Allen Browne]

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the argument
SaveRecord. If you still get error messages that Save is not available, you
could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command button.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish my
tasks, so I am going to try to work with Jerry's reply, which is at my
level! But your code may enlighten me as to another issue I have. Does
"dirty" mean the record has been edited and not yet saved? Because I have
situations where upon clicking a command button on a form, the first macro
step is to save the record. But if the user happens to be a read-only
user, this blows up. Could I make a macro condition that says if the
record is dirty, then save? (If not, which would be the case if the user
was a read-only user, I could have the macro continue from there, skipping
the save step) How would my conditional phrase look if I did that?

In the Click event of the button on your form, you can set the form's
filter so the SaleDate field contains only this week's records like this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the same
strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make a
query that filters to display on any given day, only records whose events
are scheduled for the current week only.

For example, if they click this action button any time during the week,
the filter will return records with events scheduled for the prior
Sunday through the following Saturday.

 

[Kathy Webster]

Thanks very much, Allen. I tried just putting [Dirty] as the macro condition
before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply to
Jerry). Then I tried your statement in my macro's WHERE CONDITION argument
in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() - Weekday(Date()) +
7)

But that didn't work either. It returned zero records when there should have
been 5 matches. (I am starting with [Date] because the name of the date
field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the argument
SaveRecord. If you still get error messages that Save is not available,
you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command
button.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish my
tasks, so I am going to try to work with Jerry's reply, which is at my
level! But your code may enlighten me as to another issue I have. Does
"dirty" mean the record has been edited and not yet saved? Because I have
situations where upon clicking a command button on a form, the first
macro step is to save the record. But if the user happens to be a
read-only user, this blows up. Could I make a macro condition that says
if the record is dirty, then save? (If not, which would be the case if
the user was a read-only user, I could have the macro continue from
there, skipping the save step) How would my conditional phrase look if I
did that?

In the Click event of the button on your form, you can set the form's
filter so the SaleDate field contains only this week's records like
this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the same
strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make a
query that filters to display on any given day, only records whose
events are scheduled for the current week only.

For example, if they click this action button any time during the week,
the filter will return records with events scheduled for the prior
Sunday through the following Saturday.

 

[Allen Browne]

Jerry's idea was to put it in the query, in which case your form is
permanently filtered like that.

If you want to option to filter it, the code is *much* more flexible than
the macro. To set it up:
1. Open your form in design view.

2. Right-click the command button you use to filter this form, and choose
Properties.

3. On the Event tab of the Properties box, set the On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the Code window.

5. Between the "Private Sub ..." and "End Sub" lines, paste the suggested
code.

That should take care of it. Note that this is ONE line, even though it
probably breaks into 2 when posted in the newsgroup:

strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much, Allen. I tried just putting [Dirty] as the macro
condition before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply to
Jerry). Then I tried your statement in my macro's WHERE CONDITION argument
in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() - Weekday(Date())
+ 7)

But that didn't work either. It returned zero records when there should
have been 5 matches. (I am starting with [Date] because the name of the
date field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the
argument SaveRecord. If you still get error messages that Save is not
available, you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command
button.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish my
tasks, so I am going to try to work with Jerry's reply, which is at my
level! But your code may enlighten me as to another issue I have. Does
"dirty" mean the record has been edited and not yet saved? Because I
have situations where upon clicking a command button on a form, the
first macro step is to save the record. But if the user happens to be a
read-only user, this blows up. Could I make a macro condition that says
if the record is dirty, then save? (If not, which would be the case if
the user was a read-only user, I could have the macro continue from
there, skipping the save step) How would my conditional phrase look if I
did that?


In the Click event of the button on your form, you can set the form's
filter so the SaleDate field contains only this week's records like
this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the same
strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make a
query that filters to display on any given day, only records whose
events are scheduled for the current week only.

For example, if they click this action button any time during the
week, the filter will return records with events scheduled for the
prior Sunday through the following Saturday.

 

[Kathy Webster]

You are very kind to have answered in such clear detail. The reason I'm not
putting it in the underlying query or form itself is because the form opens
to all records. Then a combo box allows the user to make choices:

-All Dates
-Today Only
-Today Forward
-This Week Only

So I have the combo box set to run a macro On Update. The macro has
conditions:

CONDITION ACTION WHERE
CONDITION
[comboBox]="Today Only" Apply Filter [Date]=Date()
[comboBox]="Today Forward" Apply Filter [Date]>=Date()
[comboBox]="All Dates" RemoveFilterSort
[comboBox]="This Week Only" ApplyFilter _________(help!)


With this in mind, can you fill in the blank, or show me a way with code to
do this? I am pretty wet behind the ears when it comes to writing actual
code. :-|

Thanks again,
Kathy

Jerry's idea was to put it in the query, in which case your form is
permanently filtered like that.

If you want to option to filter it, the code is *much* more flexible than
the macro. To set it up:
1. Open your form in design view.

2. Right-click the command button you use to filter this form, and choose
Properties.

3. On the Event tab of the Properties box, set the On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the Code window.

5. Between the "Private Sub ..." and "End Sub" lines, paste the suggested
code.

That should take care of it. Note that this is ONE line, even though it
probably breaks into 2 when posted in the newsgroup:

strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much, Allen. I tried just putting [Dirty] as the macro
condition before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply to
Jerry). Then I tried your statement in my macro's WHERE CONDITION
argument in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7)

But that didn't work either. It returned zero records when there should
have been 5 matches. (I am starting with [Date] because the name of the
date field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the
argument SaveRecord. If you still get error messages that Save is not
available, you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command
button.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish my
tasks, so I am going to try to work with Jerry's reply, which is at my
level! But your code may enlighten me as to another issue I have. Does
"dirty" mean the record has been edited and not yet saved? Because I
have situations where upon clicking a command button on a form, the
first macro step is to save the record. But if the user happens to be a
read-only user, this blows up. Could I make a macro condition that says
if the record is dirty, then save? (If not, which would be the case if
the user was a read-only user, I could have the macro continue from
there, skipping the save step) How would my conditional phrase look if
I did that?


In the Click event of the button on your form, you can set the form's
filter so the SaleDate field contains only this week's records like
this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the
same strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make a
query that filters to display on any given day, only records whose
events are scheduled for the current week only.

For example, if they click this action button any time during the
week, the filter will return records with events scheduled for the
prior Sunday through the following Saturday.

 

[Allen Browne]

Try:
[Date] Between Date() - Weekday(Date()) + 1 And Date() - Weekday(Date())
+ 7

(BTW, I hope you don't really have a field named Date. That is a reserved
word in VBA, and Access will get confused about whether you are referring to
the system date or the field named date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You are very kind to have answered in such clear detail. The reason I'm
not putting it in the underlying query or form itself is because the form
opens to all records. Then a combo box allows the user to make choices:

-All Dates
-Today Only
-Today Forward
-This Week Only

So I have the combo box set to run a macro On Update. The macro has
conditions:

CONDITION ACTION WHERE
CONDITION
[comboBox]="Today Only" Apply Filter [Date]=Date()
[comboBox]="Today Forward" Apply Filter [Date]>=Date()
[comboBox]="All Dates" RemoveFilterSort
[comboBox]="This Week Only" ApplyFilter _________(help!)


With this in mind, can you fill in the blank, or show me a way with code
to do this? I am pretty wet behind the ears when it comes to writing
actual code. :-|

Thanks again,
Kathy

Jerry's idea was to put it in the query, in which case your form is
permanently filtered like that.

If you want to option to filter it, the code is *much* more flexible than
the macro. To set it up:
1. Open your form in design view.

2. Right-click the command button you use to filter this form, and choose
Properties.

3. On the Event tab of the Properties box, set the On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the Code window.

5. Between the "Private Sub ..." and "End Sub" lines, paste the suggested
code.

That should take care of it. Note that this is ONE line, even though it
probably breaks into 2 when posted in the newsgroup:

strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much, Allen. I tried just putting [Dirty] as the macro
condition before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply to
Jerry). Then I tried your statement in my macro's WHERE CONDITION
argument in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7)

But that didn't work either. It returned zero records when there should
have been 5 matches. (I am starting with [Date] because the name of the
date field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the
argument SaveRecord. If you still get error messages that Save is not
available, you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command
button.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish
my tasks, so I am going to try to work with Jerry's reply, which is at
my level! But your code may enlighten me as to another issue I have.
Does "dirty" mean the record has been edited and not yet saved?
Because I have situations where upon clicking a command button on a
form, the first macro step is to save the record. But if the user
happens to be a read-only user, this blows up. Could I make a macro
condition that says if the record is dirty, then save? (If not, which
would be the case if the user was a read-only user, I could have the
macro continue from there, skipping the save step) How would my
conditional phrase look if I did that?


In the Click event of the button on your form, you can set the form's
filter so the SaleDate field contains only this week's records like
this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the
same strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make
a query that filters to display on any given day, only records whose
events are scheduled for the current week only.

For example, if they click this action button any time during the
week, the filter will return records with events scheduled for the
prior Sunday through the following Saturday.

 

[Kathy Webster]

Perfect - thank you!!
....and yes I do have a field named Date. I've been using it since Access97
with so far no bad results, but as you know I don't write code, I do it all
through queries, macros, etc. Is that why I haven't had a problem? Where can
I find a list of "reserved" words to find out what other horrors await me???

Try:
[Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7

(BTW, I hope you don't really have a field named Date. That is a reserved
word in VBA, and Access will get confused about whether you are referring
to the system date or the field named date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You are very kind to have answered in such clear detail. The reason I'm
not putting it in the underlying query or form itself is because the form
opens to all records. Then a combo box allows the user to make choices:

-All Dates
-Today Only
-Today Forward
-This Week Only

So I have the combo box set to run a macro On Update. The macro has
conditions:

CONDITION ACTION WHERE
CONDITION
[comboBox]="Today Only" Apply Filter [Date]=Date()
[comboBox]="Today Forward" Apply Filter [Date]>=Date()
[comboBox]="All Dates" RemoveFilterSort
[comboBox]="This Week Only" ApplyFilter _________(help!)


With this in mind, can you fill in the blank, or show me a way with code
to do this? I am pretty wet behind the ears when it comes to writing
actual code. :-|

Thanks again,
Kathy

Jerry's idea was to put it in the query, in which case your form is
permanently filtered like that.

If you want to option to filter it, the code is *much* more flexible
than the macro. To set it up:
1. Open your form in design view.

2. Right-click the command button you use to filter this form, and
choose Properties.

3. On the Event tab of the Properties box, set the On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the Code window.

5. Between the "Private Sub ..." and "End Sub" lines, paste the
suggested code.

That should take care of it. Note that this is ONE line, even though it
probably breaks into 2 when posted in the newsgroup:

strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much, Allen. I tried just putting [Dirty] as the macro
condition before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply to
Jerry). Then I tried your statement in my macro's WHERE CONDITION
argument in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7)

But that didn't work either. It returned zero records when there should
have been 5 matches. (I am starting with [Date] because the name of the
date field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the
argument SaveRecord. If you still get error messages that Save is not
available, you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command
button.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish
my tasks, so I am going to try to work with Jerry's reply, which is
at my level! But your code may enlighten me as to another issue I
have. Does "dirty" mean the record has been edited and not yet saved?
Because I have situations where upon clicking a command button on a
form, the first macro step is to save the record. But if the user
happens to be a read-only user, this blows up. Could I make a macro
condition that says if the record is dirty, then save? (If not, which
would be the case if the user was a read-only user, I could have the
macro continue from there, skipping the save step) How would my
conditional phrase look if I did that?


In the Click event of the button on your form, you can set the
form's filter so the SaleDate field contains only this week's
records like this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the
same strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make
a query that filters to display on any given day, only records whose
events are scheduled for the current week only.

For example, if they click this action button any time during the
week, the filter will return records with events scheduled for the
prior Sunday through the following Saturday.

 

[Kathy Webster]

Sorry to be so lame, but I'm experimenting with your formula to try to
obtain dates for "Next Week Only." I tried

[Date] Between Date() + Weekday(Date()) + 7 And Date() - Weekday(Date())
+ 14

....but since I don't know what I'm doing, it failed. Since I can't find any
documentation in Help(less) on this, it seems my only help is coming from
Down Under

Try:
[Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7

(BTW, I hope you don't really have a field named Date. That is a reserved
word in VBA, and Access will get confused about whether you are referring
to the system date or the field named date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You are very kind to have answered in such clear detail. The reason I'm
not putting it in the underlying query or form itself is because the form
opens to all records. Then a combo box allows the user to make choices:

-All Dates
-Today Only
-Today Forward
-This Week Only

So I have the combo box set to run a macro On Update. The macro has
conditions:

CONDITION ACTION WHERE
CONDITION
[comboBox]="Today Only" Apply Filter [Date]=Date()
[comboBox]="Today Forward" Apply Filter [Date]>=Date()
[comboBox]="All Dates" RemoveFilterSort
[comboBox]="This Week Only" ApplyFilter _________(help!)


With this in mind, can you fill in the blank, or show me a way with code
to do this? I am pretty wet behind the ears when it comes to writing
actual code. :-|

Thanks again,
Kathy

Jerry's idea was to put it in the query, in which case your form is
permanently filtered like that.

If you want to option to filter it, the code is *much* more flexible
than the macro. To set it up:
1. Open your form in design view.

2. Right-click the command button you use to filter this form, and
choose Properties.

3. On the Event tab of the Properties box, set the On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the Code window.

5. Between the "Private Sub ..." and "End Sub" lines, paste the
suggested code.

That should take care of it. Note that this is ONE line, even though it
probably breaks into 2 when posted in the newsgroup:

strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much, Allen. I tried just putting [Dirty] as the macro
condition before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply to
Jerry). Then I tried your statement in my macro's WHERE CONDITION
argument in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7)

But that didn't work either. It returned zero records when there should
have been 5 matches. (I am starting with [Date] because the name of the
date field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the
argument SaveRecord. If you still get error messages that Save is not
available, you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command
button.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish
my tasks, so I am going to try to work with Jerry's reply, which is
at my level! But your code may enlighten me as to another issue I
have. Does "dirty" mean the record has been edited and not yet saved?
Because I have situations where upon clicking a command button on a
form, the first macro step is to save the record. But if the user
happens to be a read-only user, this blows up. Could I make a macro
condition that says if the record is dirty, then save? (If not, which
would be the case if the user was a read-only user, I could have the
macro continue from there, skipping the save step) How would my
conditional phrase look if I did that?


In the Click event of the button on your form, you can set the
form's filter so the SaleDate field contains only this week's
records like this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the
same strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make
a query that filters to display on any given day, only records whose
events are scheduled for the current week only.

For example, if they click this action button any time during the
week, the filter will return records with events scheduled for the
prior Sunday through the following Saturday.

 

[Guest]

Kathy,

Try

[Date] between Date() + (8-Weekday(Date())) AND Date() + (15 -
Weekday(Date()))

It may be 14 instead of 15, but I think 15 is what you want.

HTH
Dale

Sorry to be so lame, but I'm experimenting with your formula to try to
obtain dates for "Next Week Only." I tried

[Date] Between Date() + Weekday(Date()) + 7 And Date() - Weekday(Date())
+ 14

....but since I don't know what I'm doing, it failed. Since I can't find any
documentation in Help(less) on this, it seems my only help is coming from
Down Under

Try:
[Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7

(BTW, I hope you don't really have a field named Date. That is a reserved
word in VBA, and Access will get confused about whether you are referring
to the system date or the field named date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You are very kind to have answered in such clear detail. The reason I'm
not putting it in the underlying query or form itself is because the form
opens to all records. Then a combo box allows the user to make choices:

-All Dates
-Today Only
-Today Forward
-This Week Only

So I have the combo box set to run a macro On Update. The macro has
conditions:

CONDITION ACTION WHERE
CONDITION
[comboBox]="Today Only" Apply Filter [Date]=Date()
[comboBox]="Today Forward" Apply Filter [Date]>=Date()
[comboBox]="All Dates" RemoveFilterSort
[comboBox]="This Week Only" ApplyFilter _________(help!)


With this in mind, can you fill in the blank, or show me a way with code
to do this? I am pretty wet behind the ears when it comes to writing
actual code. :-|

Thanks again,
Kathy

Jerry's idea was to put it in the query, in which case your form is
permanently filtered like that.

If you want to option to filter it, the code is *much* more flexible
than the macro. To set it up:
1. Open your form in design view.

2. Right-click the command button you use to filter this form, and
choose Properties.

3. On the Event tab of the Properties box, set the On Click property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the Code window.

5. Between the "Private Sub ..." and "End Sub" lines, paste the
suggested code.

That should take care of it. Note that this is ONE line, even though it
probably breaks into 2 when posted in the newsgroup:

strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much, Allen. I tried just putting [Dirty] as the macro
condition before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply to
Jerry). Then I tried your statement in my macro's WHERE CONDITION
argument in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7)

But that didn't work either. It returned zero records when there should
have been 5 matches. (I am starting with [Date] because the name of the
date field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the
argument SaveRecord. If you still get error messages that Save is not
available, you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the best
solution unless you need to be able to turn it on/off with a command
button.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my head
,since I work mostly in macros and filters and queries to accomplish
my tasks, so I am going to try to work with Jerry's reply, which is
at my level! But your code may enlighten me as to another issue I
have. Does "dirty" mean the record has been edited and not yet saved?
Because I have situations where upon clicking a command button on a
form, the first macro step is to save the record. But if the user
happens to be a read-only user, this blows up. Could I make a macro
condition that says if the record is dirty, then save? (If not, which
would be the case if the user was a read-only user, I could have the
macro continue from there, skipping the save step) How would my
conditional phrase look if I did that?


In the Click event of the button on your form, you can set the
form's filter so the SaleDate field contains only this week's
records like this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use the
same strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to make
a query that filters to display on any given day, only records whose
events are scheduled for the current week only.

For example, if they click this action button any time during the
week, the filter will return records with events scheduled for the
prior Sunday through the following Saturday.

 

[Kathy Webster]

Fantastic! Thank you - It's perfect. Maybe with a little luck I will start
to see the pattern here

Kathy,

Try

[Date] between Date() + (8-Weekday(Date())) AND Date() + (15 -
Weekday(Date()))

It may be 14 instead of 15, but I think 15 is what you want.

HTH
Dale

Sorry to be so lame, but I'm experimenting with your formula to try to
obtain dates for "Next Week Only." I tried

[Date] Between Date() + Weekday(Date()) + 7 And Date() - Weekday(Date())
+ 14

....but since I don't know what I'm doing, it failed. Since I can't find
any
documentation in Help(less) on this, it seems my only help is coming from
Down Under

Try:
[Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7

(BTW, I hope you don't really have a field named Date. That is a
reserved
word in VBA, and Access will get confused about whether you are
referring
to the system date or the field named date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You are very kind to have answered in such clear detail. The reason
I'm
not putting it in the underlying query or form itself is because the
form
opens to all records. Then a combo box allows the user to make
choices:

-All Dates
-Today Only
-Today Forward
-This Week Only

So I have the combo box set to run a macro On Update. The macro has
conditions:

CONDITION ACTION WHERE
CONDITION
[comboBox]="Today Only" Apply Filter
[Date]=Date()
[comboBox]="Today Forward" Apply Filter [Date]>=Date()
[comboBox]="All Dates" RemoveFilterSort
[comboBox]="This Week Only" ApplyFilter
_________(help!)


With this in mind, can you fill in the blank, or show me a way with
code
to do this? I am pretty wet behind the ears when it comes to writing
actual code. :-|

Thanks again,
Kathy

Jerry's idea was to put it in the query, in which case your form is
permanently filtered like that.

If you want to option to filter it, the code is *much* more flexible
than the macro. To set it up:
1. Open your form in design view.

2. Right-click the command button you use to filter this form, and
choose Properties.

3. On the Event tab of the Properties box, set the On Click property
to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the Code window.

5. Between the "Private Sub ..." and "End Sub" lines, paste the
suggested code.

That should take care of it. Note that this is ONE line, even though
it
probably breaks into 2 when posted in the newsgroup:

strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1 And
Date() - Weekday(Date()) + 7"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much, Allen. I tried just putting [Dirty] as the macro
condition before the RunCommand action and it and it works great.

Are you still willing to help with my original problem? I tried the
statement Jerry suggested, and it didn't work (please see my reply
to
Jerry). Then I tried your statement in my macro's WHERE CONDITION
argument in this format:

=([Date] Between Date() - Weekday(Date()) + 1 And Date() -
Weekday(Date()) + 7)

But that didn't work either. It returned zero records when there
should
have been 5 matches. (I am starting with [Date] because the name of
the
date field in my form happens to be "Date")

Yes. A bound form is "dirty" when it has uncommitted edits.

To force the save in a macro, use the RunCommand action, with the
argument SaveRecord. If you still get error messages that Save is
not
available, you could avoid them by setting the Macro Condition to:
[Forms].[Form1].[Dirty]
which will be True only if Form1 has uncommitted edits.
(That condition will generate errors if Form1 is unbound.)

Putting the condition into the Criteria row of your query is the
best
solution unless you need to be able to turn it on/off with a
command
button.

Thanks so much. Allen, Jerry and Dale. Allen is a little over my
head
,since I work mostly in macros and filters and queries to
accomplish
my tasks, so I am going to try to work with Jerry's reply, which
is
at my level! But your code may enlighten me as to another issue I
have. Does "dirty" mean the record has been edited and not yet
saved?
Because I have situations where upon clicking a command button on
a
form, the first macro step is to save the record. But if the user
happens to be a read-only user, this blows up. Could I make a
macro
condition that says if the record is dirty, then save? (If not,
which
would be the case if the user was a read-only user, I could have
the
macro continue from there, skipping the save step) How would my
conditional phrase look if I did that?


In the Click event of the button on your form, you can set the
form's filter so the SaleDate field contains only this week's
records like this:

Private Sub cmdThisWeek_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits first.
strWhere = "[SaleDate] Between Date() - Weekday(Date()) + 1
And
Date() - Weekday(Date()) + 7"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you actually wanted to open a report instead, you would use
the
same strWhere string above like this:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

I have a table of dates and events. I would like to know how to
make
a query that filters to display on any given day, only records
whose
events are scheduled for the current week only.

For example, if they click this action button any time during
the
week, the filter will return records with events scheduled for
the
prior Sunday through the following Saturday.

 

[Gary Walter]

:

Maybe with a little luck I will start to see the pattern here

<snip>

Hi Kathy,

PMFBI

"The pattern" starts with subtracting
the weekday of any date from that date

Date() - WeekDay(Date())

---> you end up on

Saturday of previous week for that date

From there you just add (or subtract)
day(s) to get to date you want.

12:00 AM of Saturday of last week= Date() - WeekDay(Date())
12:00 AM of Sunday of this week= Date() - WeekDay(Date()) +1
12:00 AM of Monday of this week=Date() - WeekDay(Date())+2
<etc.>
12:00 AM of Friday of this week=Date() - WeekDay(Date())+6
12:00 AM of Saturday of this week= Date() - WeekDay(Date())+7

next week ex:

12:00 AM of Sunday of next week=Date() - WeekDay(Date())+8

last week ex:

12:00 AM of Friday of last week =Date() - WeekDay(Date())-1

============
the above used today's date function Date()

but sometimes you may have a datetime field
(say "MyDate")
that you want to include a calculated field in a
query that returns maybe the start of the week
(Sunday) for each "MyDate"

StartOfWk: [MyDate] - WeekDay([MyDate])+1

Apologies again for butting in,

gary

 

[Kathy Webster]

Thank you so much, Gary! You are very kind to "butt in" with such great
information!
Hai to Gary!

:

Maybe with a little luck I will start to see the pattern here

<snip>

Hi Kathy,

PMFBI

"The pattern" starts with subtracting
the weekday of any date from that date

Date() - WeekDay(Date())

---> you end up on

Saturday of previous week for that date

From there you just add (or subtract)
day(s) to get to date you want.

12:00 AM of Saturday of last week= Date() - WeekDay(Date())
12:00 AM of Sunday of this week= Date() - WeekDay(Date()) +1
12:00 AM of Monday of this week=Date() - WeekDay(Date())+2
<etc.>
12:00 AM of Friday of this week=Date() - WeekDay(Date())+6
12:00 AM of Saturday of this week= Date() - WeekDay(Date())+7

next week ex:

12:00 AM of Sunday of next week=Date() - WeekDay(Date())+8

last week ex:

12:00 AM of Friday of last week =Date() - WeekDay(Date())-1

============
the above used today's date function Date()

but sometimes you may have a datetime field
(say "MyDate")
that you want to include a calculated field in a
query that returns maybe the start of the week
(Sunday) for each "MyDate"

StartOfWk: [MyDate] - WeekDay([MyDate])+1

Apologies again for butting in,

gary