quarterly, excludes weekends and holidays

[madagaluna]

Hi,

I am building a database for work on ACCESS 2007, although will convert it to ACCESS 2000 due to our archiac computer system.

The database will calculate the next quarter (from today's date) for a date previously entered on a table ( [ss_WIRB_IRB_2_Approval]![Date2Approved] ).

I have tried the following:

Review Due:
Year([ss_WIRB_IRB_2_Approval]![Date2Approved])*4+DatePart("q",[ss_WIRB_IRB_2_Approval]![Date2Approved])=Year(Date())*4+DatePart(
"q",Date())+1

which returns "0", which I find very perplexing. Does anyone see my flaw?

To complicate matters, I want to push the Review DUe to the next Monday if it happens to fall on a weekend and to the next workday if it happens to fall on a weekend. I have found a script that could be incorporated for weekends, although I am not sure where to put it and I don't know what the ?? would be:



iif(datepart (“w”, date) <= ?? , date +1, date)


I'm primarily using the wizard, although I know a little SQL.

Any help would be greatly appreciated.

Thanks!

[ajetrumpet]

do the formulas in the attachment help?

[jzwp11]

You cannot have an = sign in the middle of an expression:

Year([ss_WIRB_IRB_2_Approval]![Date2Approved])*4+DatePart("q",[ss_WIRB_IRB_2_Approval]![Date2Approved])=Year(Date())*4+DatePart(
"q",Date())+1

If we were to evaluate the above equation using a date of 1/3/2011 for date2approved and today's date; this is what you would get:

Year(1/3/2011)*4+1=Year(4/1/2011)*4+2+1
or
8044+1=8044+2+1
or
8045=8047


I am confused as to what your trying to do & where you are trying to do it (in a form, query or report)?:

The database will calculate the next quarter (from today's date) for a date previously entered on a table ( [ss_WIRB_IRB_2_Approval]![Date2Approved] ).

If you want to get the quarter number for the the next quarter relative to today's date that can be done as follows:

datepart("q", dateadd("q",1,date()))

I'm just not sure what you want to do next...