Table Design - Date Question

[Guest]

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

 

[Guest]

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

 

[Jamie Collins]

replace the =4, =5, =6, with the more readable

=vbWednesday, =vbThursday, =vbFriday

As this is a thread titled 'Table Design' in the group named
'tablesdbdesign', I would advise you to avoid using such constants in
code within database objects, particularly tables (e.g. validation
rules). Consider using the text values 'Monday', 'Tuesday', etc which
would improve readability while leaving the Jet database objects
independent of the Access interface, as they should be.

Jamie.

--

 

[Guest]

Ingalla,

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

Sprinks