Business Days

[Blue Falcon]

A user inputs a date and I need to have the program search for data matching the date inputted as well as anything 5 business days prior (minus holidays as well).

any idea how to code this?

[antonye]

Erm ... you might want to be a little bit more specific, like telling us which programming language you are using, if you're using a database and what sort, or did you just want a solution in anything?

[Blue Falcon]

I'm using Access 97.

[antonye]

Originally posted by Blue Falcon:I'm using Access 97.

OK ... so that's your database, but are you writing your own forms, using Access Reports, using a Visual Basic front end, doing an SQL Query...?

Give us some clues!

[Blue Falcon]

My Bad. I'm just using plain ol' Access 97 to do everything. Nothing extra.

[Blue Falcon]

Well, I guess I could use VB as a module.


You'll have to forgive me, I'm just learning Access.

[antonye]

In your form, where the date is input, you can use the DateAdd() (http://msdn.microsoft.com/library/officedev/office97/output/F1/D6/S5B206.HTM) internal function to find the entered date -5 days:


sStartDate = DateAdd("d", -5, sInputDate)


If you want to ensure that the sStartDate is a workday (ie, mon-fri) then you want to use the Format() (http://msdn.microsoft.com/library/officedev/office97/output/F1/D6/S5B22A.HTM) function (See Format Characters (http://msdn.microsoft.com/library/officedev/office97/output/F1/D6/S5B22F.HTM)):


SELECT CASE (Format(sStartDate, "w"))
CASE 1:
' this is a sunday
sStartDate = DateAdd("d", -2, sStartDate)
CASE 7:
' this is a saturday
sStartDate = DateAdd("d", -1, sStartDate)
END SELECT


So what we've done is changed it to the Friday if it's a saturday or sunday.

You can now safely search between your two dates.

In SQL:

... WHERE [EntryDate] BETWEEN [STARTDATE] AND [INPUTDATE]

[Blue Falcon]

Ok, I just learned that it is for 20 business days, not just 5. Any suggestions?