Conditional default date value in Access database

Question

Hi, Microsoft community.

I have an Access database where I process user requests every weekday.  The requests are created on the previous business date, and the database has a field titled CreationDate.

I would like to make the default value one business day prior to the current date; in other words, on Mondays the default value should be Date()-3; on all other days the default value should be Date()-1.

I tried using a default value statement of the following, but it did not work.

=IIf(Weekday(Date()=2),Date()-3,Date()-1)

Regardless of the date, it put a default value of three days prior to the current date into the field.

How can I create a conditional default date value to satisfy the business process?

Thank you very much.

Errol C. Isenberg

Certified Microsoft Office Specialist Excel 2013

******@gmail.com

Answer 1

A problem with a simple expression like that is that it takes no account of public or concessionary holidays.  The best solution is to have an auxiliary calendar table of all work days, i.e. a simple table of all dates bar weekends and any holiday dates, extending 10 or so years into the future (it can easily be extended if necessary).  Future holiday dates need to be deleted as and when they become known of course.  You'll find ways to build such a table in Calendar.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

Armed with the calendar table, called WorkDays say, you can then return the preceding working day with:

=DMax("calDate","WorkDays","calDate < Date()")

PS:  I'd recommend assigning the date in the form's BeforeInsert event procedure.

Answer 2

What about that didn't work? The expression seems correct. did you enter that expression in the DefaultValue property?

Alternatively you can put it int he On Current event:

If Me.NewRecord Then

    If Weekday(Date()=2) then

          Me.CreateDate = DateAdd("d",-3,Date())

    Else

          Me.CreateDate = DateAdd("d",-1,Date())

    End If

End If