Extract logs over 30 working days

[Lyncroft]

I'm pulling out logs over 30 days old using <date()-30. The user wants the 30 to be working days. Any ideas how to go about this. I've looked at Mile's thread below but cant quite see how to incorporate it into my needs. Am I on the right track.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=53842

 

[Mile-O]

Which thread, the link does not work?

 

[Lyncroft]

Yeah, sorry Mile. Don't know why that is.


Anyway, it's the post by Moonshine about 8 threads down titled
"Calculate working days"

 

[Mile-O]

This thread contains a count forward 30 days excluding holidays and weekends, it could be modified slightly to go back 30 days instead.

 

[Lyncroft]

Cheers as ever Mile-O-Phile. I'll investigate.

 

[Mile-O]

This, I think, is the only line you'll need to change:

dteTemp = dteTemp + 1

to:

dteTemp = dteTemp - 1

Possibly the line underneath by reversing the arithmetic operator also.

 

[Lyncroft]

Finally got it to work!!! Only took the whole morning. Cheers Mile. The way I went about it meant I didn't have to change your code. But hey, the query works!

 

[Mile-O]

Used a negative value?

 

[Lyncroft]

Oh no. Nothing as advanced as that!

Probably not the way to go but what I did in the query grid was have a field when the log was opened, a field that calculated plus 30 working days on the log date, a field with todays date. To extract those over 30 working days (ie, overdue), I took out those records where the due date is less than todays date. Clunky but I think it works!!!