Before Update

[Radhika]

I have a form with a field called 'Date'. I want those entering data into
this form to only be able to enter dates between 1/1/2009 and 1/31/2009.

This is what I tried:
In the before update event of 'Date' I put in the following code:
Private Sub Date_BeforeUpdate(Cancel As Integer)
Const ConMESSAGE = _
"Please enter a date > 1/1/2009 and <1/31/2009"

If Not IsNull(Me.[Date]) Then
If [Date] > "1/31/2009" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub

However, this only gives me an error msg if the date is not 1/31/2009. What
am i doing wrong?
Thank you
Radhika

 

[Jeanette Cunningham]

Hi Radhika,
try it like this-->

If [Date] > "1/31/2009" Or [Date] < "1/2/2009"
Then . . . .


By the way, I hope that your field is not called 'Date'.
Date is a reserved word in Access. Using it as a field name will get access
confused and cause problems now and then.
Name suggestions: OpenDate, PayDate, ReturnDate etc

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jerry Whittle]

"1/31/2009" is text and not a date. Try #1/31/2009#

Also it won't stop someone from putting in a date previous to 2009.

Why not just create a validation rule at table level and let the database
handle it?

Also Date is a reserved word so that might mess you up; however, the []
should stop that from happening.

 

[Douglas J. Steele]

Literal dates in Access need to be delimited with #, not quotes.

As well, you really should rename your field. Date is a reserved word, and
you should never use reserved words for your own purposes. For a
comprehensive list of names to avoid (as well as a link to a free utility to
check your application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Graham Mandeno]

Hi Radhika

It is very dangerous to give a field the same name as a reserved work in
Access. The most common ones causing problems are "Name" and "Date".

If at all possible, you should change the name of your "Date" field to
"OrderDate", or whatever is appropriate to describe the date.

If not, then refer to the "Date" control on your form as Me![Date], but
don't be surprised if you run into further problems later.

What is happening is that [Date] by itself is calling the built-in function
Date(), which returns *today's* date.

Also, using a string "1/31/2009" may cause failure depending on your
computer's date format settings. You should use the date constant
delimiters (#). #1/31/2009# will mean 31-January-2009 in any country.

However, you are testing to make sure that the date entered is in the month
of January, 2009, so instead of testing for start and end dates you could do
this:
If Format(Me![DateField], "yyyymm") <> "200901" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

 

[Radhika]

Thank you!

Those were all helpful!

Hi Radhika

It is very dangerous to give a field the same name as a reserved work in
Access. The most common ones causing problems are "Name" and "Date".

If at all possible, you should change the name of your "Date" field to
"OrderDate", or whatever is appropriate to describe the date.

If not, then refer to the "Date" control on your form as Me![Date], but
don't be surprised if you run into further problems later.

What is happening is that [Date] by itself is calling the built-in function
Date(), which returns *today's* date.

Also, using a string "1/31/2009" may cause failure depending on your
computer's date format settings. You should use the date constant
delimiters (#). #1/31/2009# will mean 31-January-2009 in any country.

However, you are testing to make sure that the date entered is in the month
of January, 2009, so instead of testing for start and end dates you could do
this:
If Format(Me![DateField], "yyyymm") <> "200901" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form with a field called 'Date'. I want those entering data into
this form to only be able to enter dates between 1/1/2009 and 1/31/2009.

This is what I tried:
In the before update event of 'Date' I put in the following code:
Private Sub Date_BeforeUpdate(Cancel As Integer)
Const ConMESSAGE = _
"Please enter a date > 1/1/2009 and <1/31/2009"

If Not IsNull(Me.[Date]) Then
If [Date] > "1/31/2009" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub

However, this only gives me an error msg if the date is not 1/31/2009.
What
am i doing wrong?
Thank you
Radhika

 

[Johannes Blümer]

I have a form with a field called 'Date'. I want those entering data into
this form to only be able to enter dates between 1/1/2009 and 1/31/2009.

This is what I tried:
In the before update event of 'Date' I put in the following code:
Private Sub Date_BeforeUpdate(Cancel As Integer)
Const ConMESSAGE = _
"Please enter a date > 1/1/2009 and <1/31/2009"

If Not IsNull(Me.[Date]) Then
If [Date] > "1/31/2009" Then
MsgBox ConMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub

However, this only gives me an error msg if the date is not 1/31/2009.
What
am i doing wrong?
Thank you
Radhika