= Now() – 5 working days ????

[Dragon]

I have a small query. One of the fields is a date field. I wan this query to give me only dates that are older then 5 days ago from now. So in the Date criteria cell I put: <Now()-5

What I would like to do is exclude weekends. That is to say, <Now() – 5 working days.

I have seen code on this site that counts weekdays between to given dates but I can’t figure out how, if at all, to adopt that code to this situation.

Ideas? Thanks.

 

[dan-cat]

Here's a little sample for you - note how the query pulls the value provided by the GetDate() function

 

[Dragon]

Okay, thanks. It worked. Problem is, I have no idea how it worked. You have a module as follows:

Public Function GetDate() As Date

Select Case Day(DateAdd("w", -5, Date))
Case Is = 1
GetDate = Date - 7
Case Is = 6
GetDate = Date - 6
Case Else
GetDate = Date - 5
End Select

End Function

I don’t get it. Like I said, it worked but I would like to know how. The “w” is the “interval as string”????

Thanks.

 

[dan-cat]

Looking at that code - it is completely wrong

Surprised someone hadn't picked it up

Here is the correct code

Public Function GetDate() As Date

Select Case Weekday(DateAdd("w", -5, Date))
Case Is = 1
GetDate = Date - 7
Case Is = 7
GetDate = Date - 6
Case Else
GetDate = Date - 5
End Select

First of all we need to get the value of todays date and subtract 5 days from it. We do this by using DateAdd - "w" means we are using days , -5 is the number of days we are adding, date (todays date) is the date that we are adding the days to.

However we need to make sure that the resulting value lands on a weekday not a weekend. So I used the WeekDay function. This converts the value returned from the DateAdd command to an integer corresponding to the day of the week. Thus if the value returned from the dateadd command is a sunday we get 1 if its a monday then 2 etc etc.


Thus if the dateadd function returns 1 (Sunday) we need to subtract 7 days from the current date to make sure we hit the Friday

If the dateadd function returns 7 (Saturday) we need to subtract 6 days from the current date to make sure we hit the Friday.

Before I was using the day function which returns an integer corresponding to the day of the month. The WeekDay function returns an integer corresponding to the day of the week.

 

[Dragon]

Just noticed that the code was wrong. It didn’t show up in my report because I had data dated as follows:

2/5/2004
2/4/2004
2/2/2004

And when I ran the report it showed me 2/2/2004 and older. 2/2/2004 was more then 5 business days ago so I thought the module was correct. Only after you pointed out the code was wrong did I add 2/3/2004 to the report to find the code went back 4 and not 5 days.

Thanks for clarifying the code for me.