Any help with this expression?

[ShadowsSA]

I need help setting up an expression. I need to set the default value = date + 2. But this also needs to take into account working days. I am just having problems with the syntax for this, as i would normally do this in VBA and do not have much experience with Expression Builder. And yes it does have to be done in Expression Builder.

I know this is horribly wrong... thats why I am asking for help.
IIf ((WeekDay(DateAdd ("d", 2, Date)) = 1, (DateAdd("d", 3, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) = 7, (DateAdd("d", 4, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) < 7 AND IIf (WeekDay(DateAdd ("d", 2, Date)) > 1, (DateAdd ("d", 2, Date),(DateAdd("d", 2, Date)))

I need 3 If statements:
1 for if its Saterday
1 for if its Sunday
1 for if its neither.

In VBA I would do something like this:
If WeekDay(DateAdd ("d", 2. Date)) = 1 Then Box = Date() + 3
If WeekDay(DateAdd ("d", 2. Date)) = 2 Then Box = Date() + 4
If WeekDay(DateAdd ("d", 2. Date)) > 1 And WeekDay(DateAdd ("d", 2. Date)) < 7 Then Box = Date() + 2

Please any assistance would be appreciated

 

[John_W]

Why do you need to do it in the expression builder? You know you can write a function in VBA and then call that from within the expresson builder?

 

[ShadowsSA]

It has to be done in expression builder, as other values are calculated and then inserted depending on choices made.

How do you call the function from the expression builder?

 

[Dennisk]

Don't forget you can nest your IIF statements (do not use OR)

Box = IIF(WeekDay(DateAdd ("d", 2. Date)) = 1,Date() + 3,
IIF(WeekDay(DateAdd ("d", 2. Date)) = 2,Date() + 4,
IIF(WeekDay(DateAdd ("d", 2. Date)) > 1 And WeekDay(DateAdd ("d", 2. Date)) < 7 ,Date() + 2,0),0),0)

 

[ShadowsSA]

Will give it a try. Thanks for your help

 

[John_W]

How do you call the function from the expression builder?

Scroll down to the list of Functions. As well as built-in functions, there will be a list of any public functions you have declared.

 

[ShadowsSA]

Got it to work by creating a function and referencing the function.Thanks for the help and for replying