Date Validation - First 7 days of the month

[CMA]

I'm conceiving of a validation rule that allows users to enter a date no
earlier than the first day of the previous month while the current time is
within the first 7 days of the month. Otherwise, for days 8 to 31, the only
valid date that can be entered must be within the current month (and not a
future date).

I think of it as an "IF" statement, but would like to enter it as a one-line
validation rule.

In other language, I want to do this:

If today is within the first seven days of the current month, then:
You are allowed to enter a date up as far
ago as the first day of last month, up to today
Else
You must enter a date up to as far as the first day of the current
month,
up until today.

My users enter this date onto a form - once this date is entered, it cannot
be modified.

[Shoe]

Here is somting to try:
If UserEnteredDate <= Date _
And ((Day(Date) <= 7 And Month(UserEnteredDate) = Month(Date) - 1) _
Or (Month(UserEnteredDate) = Month(Date))) Then
MsgBox "Add Code for Good Date here"
Else
MsgBox "Add Code to handle Bad Date here"
End If

Here is a sub you can copy and paste into any module and run differnt dates
through to make sure it is doing what you want.
The only difference in this code and the one above is I replaced the
"Date()" function with strFakeDate so you can make up the dates you want to
try.

Function SevenDay_test()
Dim UserEnteredDate As String
Dim strFakeDate As String
UserEnteredDate = "5/1/2008"
strFakeDate = "7/7/2008"

If UserEnteredDate <= Date _
And ((Day(strFakeDate) <= 7 And Month(UserEnteredDate) = Month(strFakeDate)
- 1) _
Or (Month(UserEnteredDate) = Month(strFakeDate))) Then
MsgBox "Add Code for Good Date here"
Else
MsgBox "Add Code to handle Bad Date here"
End If

End Function

[Klatuu]

Private Function Form_BeforeUpdate(Cancel As Integer)
Dim dtmLowDate as Date

If Day(Date) < = 7 Then
dtmLowDate = Dateserial(Year(Date), Month(Date) -1, 1)
Else
dtmLowDate = Dateserial(Year(Date), Month(Date), 1)
End If

If Me.txtEntryDate < dtmLowDate Or met.txtEntryDate > Date Then
MsgBox "Entry Date Must be Between " & dtmLowDate & _
" And " & Date
Cancel = True
End If

End Sub
--
Dave Hargis, Microsoft Access MVP

[CMA]

I've learned a bit from your example.

I've made the following modification (as a before update to the form entry
field, rather than the form), and I've experienced an unusual error. When I
attempt to enter a current or past date, a message box appears saying:

"Entry Date Must be Between 06/01/2008 And 06/30/2008"

Given that today is July 24 (as per my system clock) something weird is
going on. Any ideas?

My code follows:

Private Sub Date_Completed_BeforeUpdate(Cancel As Integer)
Dim dtmLowDate As Date

If Date_Completed.Value > 0 Then

If Day(Date) <= 7 Then

dtmLowDate = DateSerial(Year(Date), Month(Date) - 1, 1)
Else
dtmLowDate = DateSerial(Year(Date), Month(Date), 1)
End If

If Date_Completed.Value < dtmLowDate Or Date_Completed.Value > Date Then

MsgBox "Entry Date Must be Between " & dtmLowDate & _
" And " & Date
Cancel = True
End If

Else
End If

End Sub

[Klatuu]

There are a few things about your code that need either correction or
improvement.

This test means nothing:
If Date_Completed.Value > 0 Then

Assuming Date_Completed is the control, then first it is best to qualify
your control names. It should be Me.Date_Completed

You do not need to use th Value property. It is the default property.

Testing for 0 will tell you nothing. If no value has been entered into the
control it's value will be Null, but you still don't know whether it is a
valid date. If you want to ensure it is a valid date, the better test would
be:

If IsDate(Me.Date_Completed) Then

But, you don't do anything with it if it not a date. Does that mean the
control can be left empty? If so, then moving the code to the control before
update is good. If Date_Completed is required, it should be in the form
before update. The control event doesn't fire unless a user enters a value
in the control.

I would write it this way:

Private Sub Date_Completed_BeforeUpdate(Cancel As Integer)
Dim dtmLowDate As Date

If IsDate(Me.Date_Completed) Then

If Day(Date) <= 7 Then
dtmLowDate = DateSerial(Year(Date), Month(Date) - 1, 1)
Else
dtmLowDate = DateSerial(Year(Date), Month(Date), 1)
End If

If Date_Completed.Value < dtmLowDate Or Date_Completed.Value > Date
Then
MsgBox "Entry Date Must be Between " & dtmLowDate & _
" And " & Date
Cancel = True
End If

End If

End Sub

As to getting the Message with 6/20/2008 for Date, do you have a field or
control or varialbe named Date? If so, you should change it. Date is a
reserved word. It would appear you have an object named Date that has that
value and Access is getting confused. You can test that by changing the line
to:

MsgBox "Entry Date Must be Between " & dtmLowDate & _

" And " & VBA.Date

[CMA]

Thank-you very much for your clear explanations and for teaching me about
some of the basics.

The database I am using is 10 years old and I've inherited it. I've learned
how to do some (but not all) things properly, but this database will be
discontinued long before it is fully revamped. Objects Names have spaces in
them!

Using your feedback, I developed new code, and thought I would place it here
if anyone else is following this example. Use of the "VBA.Date" had to be
peppered throughout the code.

And yet, the date completed isn't a requirement, but when it is entered it
must be correctly entered.

Private Sub Date_Completed_BeforeUpdate(Cancel As Integer)
Dim dtmLowDate As Date

If IsDate(Me.Date_Completed) Then

If Day(VBA.Date) <= 7 Then
dtmLowDate = DateSerial(Year(VBA.Date), Month(VBA.Date) - 1, 1)
Else
dtmLowDate = DateSerial(Year(VBA.Date), Month(VBA.Date), 1)
End If

If Date_Completed < dtmLowDate Or Date_Completed > VBA.Date Then

MsgBox "Entry Date Must be Between " & dtmLowDate & _

" And " & VBA.Date

[Klatuu]

So the VBA.Date fixed it? Something, somewhere is named Date or you may have
lost your reference to VBA in the VB Editor. Tools, References. Make sure
Visual Basic for Applications is checked, not marked *Missing*, and it should
be the first reference listed.

Your code looks much better. Good aligned indentation makes it easier to
read. I see you have that going for you. I would suggest you indent
everything one more tab to the right. I suggest only comments, procedure
declarations, Dims, and End Sub or End Function be all the way to the left.

Also, if you don't have any code in the Else, you don't need to include it.

--
Dave Hargis, Microsoft Access MVP