Can I format a date field to enter only a week day?

[Boo]

I have a date/time field on a form.
I have a calculation that adds number of days to Date() and enters the answer in the field. Exampleate()+30.
If the answer comes on a week end, can I get the date entered to be the next occuring week day?
Thanks

 

[WayneRyan]

Boo:

Dim strTemp As String
strTemp = Format(Date + 30, "ddd")
Select Case strTemp
   Case "Sat"
      Me.txtNewDate = DateAdd("d", 32, Date)
   Case "Sun"
      Me.txtNewDate = DateAdd("d", 31, Date)
   Case Else
      Me.txtNewDate = DateAdd("d", 30, Date)
   End Select

Wayne

 

[alwainwright]

Where do I put the coding into Access?

Hi, this code is hopefully just what I've been looking for. However, I don't know how to put it into the date field in my table as I've never used coding before. Can anyone help please?

Thanks.

 

[WayneRyan]

alwain,

It goes on a form, in the BeforeUpdate event of the date field.

Wayne

 

[ScottGem]

Hi, this code is hopefully just what I've been looking for. However, I don't know how to put it into the date field in my table as I've never used coding before. Can anyone help please?

Thanks.

I would create a public function so you can use this anywhere you want

Public Function NextBizDay(dteMyDate As Date) as Date
Dim intWeekday As integer
Dim dteNewDate As Date

dtenewDate = dteMyDate+30
intWeekday = Weekday(dteNewDate)

Select Case intWeekday
   Case 1
      NextBizDate = DateAdd("d", 1, dteNewDate)
   Case 7
       NextBizDate = DateAdd("d", 2, dteNewDate)
   Case Else
       NextBizDate = dtenewDate
End Select
End Function

You would use this by placing =NextBizDate([datefield]) whereever you needed to display it. You can also make it more flexible. I.E.

Public Function NextBizDay(dteMyDate As Date, intAddDays As Integer) as Date
Dim intWeekday As integer
Dim dteNewDate As Date

dtenewDate = DateAdd("d", intAddDays,dteMyDate)
intWeekday = Weekday(dteNewDate)

Select Case intWeekday
   Case 1
      NextBizDate = DateAdd("d", 1, dteNewDate)
   Case 7
       NextBizDate = DateAdd("d", 2, dteNewDate)
   Case Else
       NextBizDate = dtenewDate
End Select
End Function

then use =NextBizDate([datefield],30). This way you can supply any interval of days that you want. You can even go further by passing an interval type so you can increment by months, weeks, years, etc.

 

[alwainwright]

Many thanks to you for your codes. I'm still struggling with where I input it into Access to get it to work in my "LoanDueDate" field in a table (I'll also need to alter the date to add 3 working days, but can hopefully adapt the code myself). I'd be grateful if anyone could supply a step-by-step idiot's guide, or point me to where I can find one as my web/forum searches have proved fruitless.

Thanks again!

 

[ScottGem]

The code goes in a global module. You should NOT have a LoanDueDate field. Since the DueDate is a calculation it can be calculated at any time using this function. As a general rule we don't store calculated values.

I think I gave pretty clear instructions on how to use the function. If you have specific questions please feel free to ask.

Not sure I follow what the 3 days is. The second version will allow you to add any number of days to the date and, if it falls on a weekend, determine the next following business day.

 

[ST4RCUTTER]

Nevermind!