show records only where date is within the next week?

[Jim001]

Dear All, I have a field named: “reminder date” in a query and would like to show only records where the reminder date is within the next week whenever the query is run (ie if todays date is 26th may 2006 and I run the query now It would only show records that include the following dates 27,28,29,30,31, july and 1,2 June)

Ive tried the below bit of code but this shows to many records beyond one week.
>=DateAdd("d",+7,Date())

Anybody have any suggestions?

Regards

Jim

 

[Jon K]

Criteria for the [Reminder Date] field:-

Between DateAdd("d", 7-Weekday(Date()), Date()) And DateAdd("d", 13-Weekday(Date()), Date())
.

 

[Jim001]

Many thanks Jon K for providing that, it seems to work great!,

Im not to sure how that code is working though, would you (or someone) mind explaining it a bit?, i dont see what the 13-Weekday part of the code is doing, why -13?

What if you were to do modify the code to show records where the reminder date is within the next coming 2 weeks instead.


id appreciate a bit of information to explain this if possible to learn from this.

Regards

Jim

 

[namliam]

First of its not -13 but +13-Weekday

Weekday returns the number of the day of the week
For a saturday it returns a 6, friday 5 and sunday is 1

The next week starts on sunday right?
Well today is Friday (5), so take the date of today - 5 days mathimaticaly you end up 26 - 5 = 21. May 21 = sunday. LAST sunday to be exact.

So to get the start of next week you add 7 days thus
DateAdd("d", 7-Weekday(Date()), Date())

To get the end of next week you might add 14 but then you end up getting a date on sunday again and Between works like >= and <= so you would get the sunday of the week after included. Therefor you should actually do 14 - 1 = 13 or not use Between and do >= and <.
Thsu you endup with
DateAdd("d", 13-Weekday(Date()), Date())

Having the total of:
Between DateAdd("d", 7-Weekday(Date()), Date()) And DateAdd("d", 13-Weekday(Date()), Date())

Or you could use
[yourfield] >= DateAdd("d", 7-Weekday(Date()), Date()) and [yourfield] <DateAdd("d", 14-Weekday(Date()), Date())

I hope this makes it clear (enough) for you

 

[Jim001]

Hi namliam, thanks for providing this in depth explanation, much appreciated!, yes I think I can see and understand how this code works now as youve explained it great here…very clever. Im going to read through this again a few times now to make sure its clear in my head. It seems logical now thinking about it that Sunday starts of with 1 and Monday 2 ect.

Cheers

Jim

 

[peterjb44]

Hi All,

I used

Between DateAdd("d",7-Weekday(Date()),Date()) And DateAdd("d",13-Weekday(Date()),Date())

in a query to show records that need a follow up, but its not working

in the query I put Company FollowUpEmail and FollowUpPhone

both FollowUpEmail and FollowUpPhone have the above put in the criteria.

these dates have been in my datebase for two weeks - 01/04/2013 is in FollowUpPhone and 08/04/2013 and 09/04/2013 which is in FollowUpEmail

I think I'm right in saying that I should of seen FollowUpPhone on 24/03/2013 and the FollowUpEmail on 31/03/2013 on my FollowUpF form with the above criteria.

any reason why they did not show?

hope this makes sense

thanks

peter

 

[jdraw]

Peter,

Do you realize that you posted in a thread whose last entry was
05-26-2006, 09:41 AM

If you have a current issue, please identify the problem so posters can understand and offer assistance.

You could try

Between Date() and DateAdd("d",7,Date) ' for this week eg next 7days

 

[peterjb44]

Hi jdraw,

yes I noticed this was a very old post.

the only thing I have found to identify why this doe's not work is because both FollowUpEmail and FollowUpPhone are on the same query with Between DateAdd("d",7-Weekday(Date()),Date()) And DateAdd("d",13-Weekday(Date()),Date()) in the criteria of each.

If I have two querys one for FollowUpEmail and the other for FollowUpPhone then it works, but then on the form I have both querys one with customer and FollowUpEmail and the other with customer and FollowUpPhone and it looks wrong to me.

is there a way to get it to work the way I had it before?

thanks

peter

 

[peterjb44]

update.....I sorted it

thanks

peter