Table Design - Date Question

[ingalla]

Can anyone tell me how to set a start date depending on the day of the week.
I have the following fields in a table,
Creation Date = todays date
Due Date = todays date + 21 days
start date

I want to get the default value for the start date calculated automatically
by taking 2 days off the due date if the weekday of the due date falls on a
Wednesday, Thursday or Friday, and by taking 4 days off the due date if that
falls on a Monday or Tuesday.

Can anyone please point me in the right direction.

Thanks
A

 

[Sprinks]

Ingalla,

You didn't mention what to do if DueDate is Saturday or Sunday, so I've
assumed that either you can't have one or it would be the DueDate - 4.

If you're using an expression to either set the value or default value of a
form control within the form properties, use:

=IIf((Weekday([DueDate])=4 Or Weekday([DueDate])=5 Or
Weekday([DueDate])=6),[DueDate]-2,[DueDate]-4)

If you're setting it in code, replace the =4, =5, =6, with the more readable

=vbWednesday, =vbThursday, =vbFriday

Hope that helps.
Sprinks

 

[Sprinks]

Ingalla,

Jamie is correct. What I meant by "code" was code behind the form, such as
in the AfterUpdate event of CreationDate.

Sprinks