Access 97/counting 20 business day

[Blue Falcon]

Ok, I posted a while back concerning 5 busniess days, but now I need to figure out how to do it for 20 business days.

Background: User inputs a before and after date and wants to search for all laws 20 business days prior to the before date as well as those between the before and after.

This is all done in Access 97.

Any suggestions?

[opiate]

SQL Statements

Please give more detail.

[Blue Falcon]

What else do you need to know?

[SubZero]

Falcon - When you post, only hit the submit button once! I have deleted 3 duplicate posts from you this morning.

Thanks.

[Blue Falcon]

But the noise my mouse makes when I click it is so neat!

Actually, my browser crashes and I'm not always sure it sends the post. Sometimes it does, sometimes it doesn't.

[L15ard]

Have you tried
Like ["please enter all or part of start date"]& "*"

then created a report from that? this will list every date after the inputed date.

can you give more info....

[Blue Falcon]

I will go and get more information for the person making the request.

[Blue Falcon]

Ok, let's do this in strictly Access. I'm not comfortable enough with SQL to try to recreate a string.

I have two dates. Received and Effective. The user inputs both dates at some point into the database. My report needs to search and dislay all Laws that were received AFTER the Effective date(that's the easy part). Including laws that were received 20 business days prior to the Effective date (the part I'm stuck on).

So, essentially I am searching for:

DateNeeded = (Effective - 20 Business days)

Then

Records Needed = all records received after DateNeeded

Does that make more sense?

If it helps, I've edited this post 3 times to get it right. <IMG SRC="smilies/smile.gif" border="0">

[Blue Falcon]

Oops, I forgot to mention that it should also take holidays into consideration as well.

I'm guessing I need a for loop to count to twenty.

and then perhaps two seperate functions. One to see if it's a weekday, the other to see if it's a holiday. I might need two counters than. One to increment to 20(the for loop) and another to count actual days. Then once I exit the loop, subtract actual days from Effective. Thus giving me my date.

[opiate]

This is all for a report? Geezzz!


<a href="http://msdn.microsoft.com/library/wcedoc/sqlce/sqlceref_5vxi.htm">DATEDIFF</a> You can use this function in a SQL statement or in VB

VB Example:

sDays = DateDiff(d, ReceivedDate, EffectiveDate)

sDays will return the number of days starting with ReceivedDate and ending with EffectiveDate. You can can create a loop to loop through all the records and if sDays = 20 then return that record for the report. Make sure you use something like the above example and include it in your loop along with the if statement.

You can also use "wk" instead of "d", "wk" can be used for the work-week.

I would suggest creating a stored procedure for this. I'm a little new to stored procedures but check out <a href="http://news.devx.com">Devx</a>

While Not rs.eof

If rs.bof then
rs.movefirst
End If

dRecived = rs.fields("reciveddate")
dEffective = rsfields("effectivedate")

sDays = DateDiff(d, dRecived, dEffective)

If sDays = 20 then

.....put the record in report

Else
rs.movenext
End If

Wend