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?
Printable View
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?
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?
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...?Quote:
Originally posted by Blue Falcon:I'm using Access 97.
Give us some clues!
My Bad. I'm just using plain ol' Access 97 to do everything. Nothing extra.
Well, I guess I could use VB as a module.
You'll have to forgive me, I'm just learning Access.
In your form, where the date is input, you can use the DateAdd() internal function to find the entered date -5 days:
If you want to ensure that the sStartDate is a workday (ie, mon-fri) then you want to use the Format() function (See Format Characters):Code:sStartDate = DateAdd("d", -5, sInputDate)
So what we've done is changed it to the Friday if it's a saturday or sunday.Code: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
You can now safely search between your two dates.
In SQL:
... WHERE [EntryDate] BETWEEN [STARTDATE] AND [INPUTDATE]
Ok, I just learned that it is for 20 business days, not just 5. Any suggestions?