Validate Date Entered is between Monday and Friday

[zestygirl]

Hello, all;

I think this is probably a simple solution for some of you.

I have a database containing records for which I need to set dates to follow up on.

I would like to ensure that the date that is entered is a business day (So that when I run my follow up report for the day - I don't end up missing items that were inadvertantly entered for a weekend!)

I have hunted around for a similar thread - but have not found anything.

I am thinking that a simple bit of validation could be entered for the field properties.

Any ideas?

Your help is greatly appreciated.

 

[CraigDolphin]

in the after update event of the date field try something like...

Private Sub myDateField_AfterUpdate()
Select Case DatePart("w", Me.myDateField)
Case vbSunday
Msgbox "Sorry....this is a Sunday!", vbinformation
Me.myDateField = "" 'deletes the bad date
Case vbSaturday
Msgbox "Sorry....this is a Saturday!", vbinformation
Me.myDateField = "" 'deletes the bad date
Case Else:
'do nothing
End Select
End Sub

...naturally, you must replace all instances of 'myDateField' with the name of the control in which you are entering your date.

or you could use a calendar as your datepicker....try visiting herehttp://www.lebans.com/monthcalendar.htm if you're interested in going this route.

 

[RuralGuy]

This bit of code:
If Weekday(Me.YourControlName, vbMonday) > 5 Then
' The date is a WeekEnd
End If

...Will detect if "YourControlName" has a weekend date in it. Is that what you wanted?

 

[zestygirl]

Thank you!!!

CraigDolphin/Rural Guy

Thanks so much to you both for solving my dilemma so fast!

I found both worked as you have indicated.

I went with CragDolphins' bit of code and it worked like a charm!

 

[RuralGuy]

I would put the code in the BeforeUpdate event and set Cancel = True if it fails so the user is held in the control.