find records with due date next monday and next week

[Misiek]

Hello,

I have been searching for past 6 hours but can't find my answer.
Have an after update event, where depending on selection a report will open and show all records with due date for next Monday, or with due date within next week.

here's my vba:

Private Sub cboAPduedate_AfterUpdate()
If cboAPduedate = "Today" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate=date()"
ElseIf cboAPduedate = "Tomorrow" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate=date()+1"
ElseIf cboAPduedate = "Saturday" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate=Date() + 7 - Weekday(Date())"
ElseIf cboAPduedate = "Sunday" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate=date() + 7 - Weekday(Date())+1"
ElseIf cboAPduedate = "Monday" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate= DatePart("ww",Date())+1 "
ElseIf cboAPduedate = "Next Week" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate= ???"
End If
End Sub

Can anyone help me please?

 

[llkhoutx]

The next week interval depends on Date() and what you consider the
next" week. Is it M-S, S-F, or whatever. It can be computed, but not with the info you provided. You can compute it in a function..

 

[Misiek]

Yes Monday-Sunday.
How to do it please, too advanced for me :/

 

[llkhoutx]

If Date() is a Monday, is the next week the next Mon-Sun?

 

[Misiek]

Yes that's right.

 

[llkhoutx]

The Monday of next week after "AnyDate" is

AnyDate + 8 - Weekday(Me.dt, vbMonday) Mod 8

Add 6 and you get the Sunday of that week.

 

[arnelgp]

you already have the correct formula for next Sunday, just follow it and that well be your next Monday.

tTaskDueDate=date() + 7 - Weekday(Date())+2

Next week would be:

tTaskDueDate=date() + 7

 

[Misiek]

llkhoutx,

php? that wouldnt work...?


arnelgp,
both give "syntax error, missing operator in query expression"

 

[llkhoutx]

What do you mean by "php?"? I gave you Access VBA code.

 

[Misiek]

llkhoutx,
No this dont work,


arnelgp,
I made a typing error, so first one works but second one didnt, so I modified it.

This works for me.

.....
ElseIf cboAPduedate = "Monday" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate=date() + 7 - Weekday(Date())+2 "
ElseIf cboAPduedate = "Next Week" Then
DoCmd.OpenReport "R_APdailyplan", acViewPreview, , "tTaskDueDate >= date() + 7 - Weekday(Date())+2 and tTaskDueDate <= date() + 7 - Weekday(Date())+7"

Thanks all for help