I have a date field on an Access form and I do not want the user to be able to put in a date that occurs in "last week's reporting period" (explained below).
Our reporting period goes from Monday to Sunday........so for example since tommorrow is Monday (the start of the new reporting period), I do not want the user to be able to put in a date for the prior 'Monday to Sunday' period.
I wanted to see if there was a way to enforce this using VBA code.
Thanks.
Date Problem
elesser
Try puting a combo box on the form which gives the user a list of dates to select. Update the list of dates when the form is openned. Below is an example from one of my databases which gives the last 7 Mondays to choose from, its not elegant but it works. WkStart is the name of the combo box.
Private Sub Form_Load()
Dim ListStr As String
Dim DateToAdd As Date
Dim x As Long
ListStr = ""
DateToAdd = LastMonday(Now())
DateToAdd = DateToAdd + 7
ListStr = Format(DateToAdd, "DD/MM/YY")
For x = 1 To 7
DateToAdd = DateToAdd - 7
ListStr = ListStr & ";" & Format(DateToAdd, "DD/MM/YY")
Next x
Me.WkStart.RowSource = ListStr
DateToAdd = LastMonday(Now())
Me.WkStart.DefaultValue = DateToAdd
Me.WkStart.Value = DateToAdd
End Sub
Function LastMonday(AnyDate) As Date
Dim x As Long
x = WeekDay(AnyDate, vbSunday)
If x > 2 Then
LastMonday = Int(AnyDate - x + 2)
Else
LastMonday = Int(AnyDate - x - 5)
End If
End Function