Criteria filter on query

[jbs]

Hi,

Im looking for a query criteria filter that will filter if 5 WORKING days have passed since the initial date. I seem to be able to do 5 days but that includes weekends.

Thanks.

 

[John Big Booty]

Have a look at the DateAdd() function you could use it to compare your start date with the current Date().
Try;

DateAdd("w", 5, Me.YourStartDate) <= Date()

 

[jbs]

i have the initial date in another column in the query, this date will be different for each row. would add date still be applicable or would i have to keep updating the criteria code?

 

[kipcliff]

I believe you will need to create a function in VBA that counts the number of days between the given date and the current date where Weekday(idatDate) is not equal to 1 or 7. This will omit the weekend, assuming your first day of the week is Sunday.

Your function should loop through each date from Start to End, test it, and increment a counter variable for each date that is a weekday (2-6). It should then return that count.

This method allows you to also test for legal holidays and omit them from the count.

One criteria will test each row on its own merit.