try this one:
Let( end = start date + 10 ; Case( DayOfweek( end ) = 6 ; end + 1 ; end ) )
that can be simplified to:
start date + 10 + ( DayOfWeek( start date + 10 ) = 6 )
Nothing wrong with your English, but obviously something wrong with my brain.
I tried this:
Let (Services::ServiceDate = Contracts::StartDate + 10; Case ( DayOfWeek (Services::ServiceDate) = 6, Services::ServiceDate + 1 ; Contracts::StartDate + 10 )))
I get an error message at :: A number, text constant, field name or "(" is expected here.
I am working with fields from 2 different tables. T Services F ServiceDate and T Contracts F StartDate
What to do?
A field name can't be a variable name !
Let ( E = Contracts::StartDate + 10 ; E + ( DayOfWeek ( E ) = 6 ) )
The field holding this calculation must be into the Contracts table.
The field I am trying to calculate sits in the 'Services' table. The field [services].[service date] is calculated 10 days from the field [contract].[start date], which is in the 'Contracts' table. As Friday is the 'day off' here, therefore, if the 10th day falls on a Friday, the service must be the following day.
Would this mean that the proposed solution would be ineffective?
Sorry to be such a pest, your kind assistance is much appreciated.
The field I am trying to calculate sits in the 'Services' table.
Why it can't stay in the "Contracts table ?
Was just the way I had set up the tables.
However, I am pleased to say that you put me on the right track. I now have a script that takes the service date, calculated from the contracts table, checks to see if the result is a friday, and if so, changes it.