Date validation not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a date range that the user needs to use. 10/1/2002 thru 9/30/2005.
They can only use the dates in this range. Nothing before 10/1/2002 and
nothing after 9/30/2005. I have tried the "Between #10/1/2002# And
#9/30/2005#" and
">= #10/1/2002# And #<= #9/30/2005#" and "Between Date()-896 And
Date()+315". None of these work. The date allow in without an error is
10/1/2002. You cannot enter anything between the range which is what I want
to do.

Is there a way to do either by a macro or SQL Statement or in a module and
what would the code look like.

Donna
 
Where are you trying to use this validation? In the table's field's
validation rule? In a form's control's validation rule? what is the data
type / format of the field / control?
 
In the form's (Properties/Validation Rule). The format on the forms is Short
Date with the input mask of 99/99/0000. The format on the table is text,
with a field size of 10. Would this be the problem?

Donna
 
If the field is text data type, then the Between .. And syntax will not
yield the result you desire, because your expression is comparing a text
string ("11/1/2005") with a date value that is "created" from a date of
10/1/2002 (which is actually the numeric value 37530). Therefore, the
comparison is not meaningful.

If you're storing the date as text, it's very difficult to use a date-based
validation rule. Is there any reason why the field can't be date/time type?
--

Ken Snell
<MS ACCESS MVP>
 
I'm not doing a comparison. This is a data entry form where the user types
in information. When they get to the date box they need to enter in a date
which should be in the range of 10/1/2002 and 9/30/2005. If they enter in a
date before 10/1/2002 then there should be a error message saying something
"You entered the wrong date". And if they entered a date after 9/30/2005 it
should also come up with a error message "You entered the wrong date".

The date entered is then stored in the table as a date without the "/".
(For ex. the entered date is 10/25/2003, the store date is 10252003.

The Access book says that the following methods should work but don't.

1. Between #10/1/2002# And #9/30/2005# or

2. >= #10/1/2002# And <= #9/30/2005# or

3. Between Date( )-896 And Date( )+315 (This is based on Wednesday's date
when I created this one.)

I have tried all 3 but can't get neither of these to work.

Got any ideas as to what else I could use? Would a macro work or SQL
statement? or a module?

Donna
 
Sorry, but our terminologies are a bit different here. Your earlier post
said that the field is a text data type with a maximum length of 10. This
post says that the field is stored as a "date" in the format of mmddyyyy --
this is a text string, not a date, in ACCESS terms. A date is stored in
ACCESS in a date/time field as a floating point number, where the integer
portion of the date is the number of days since December 30, 1899. As I
noted earlier, the date of 10/1/2002 is actually the number 37530, not the
text string 10012002.

What you're doing is storing the numbers of the date string as a text
string. As such, you cannot use a validation rule that is comparing a text
string to a date/time value -- which is what I said in my reply.

Now, if you were storing the date string in the format yyyymmdd, then you
could use a validation rule similar to this:
Between "10021001" And "20050930"

So, for your setup, I would scrap the idea of a validation rule and instead
use the BeforeUpdate event of the textbox to do the validation:

Private Sub DateTextBoxName_BeforeUpdate(Cancel As Integer)
Dim TestDate As Date
TestDate = DateSerial(Right(Me!DateTextBoxName.Value, 4), _
Left(Me!DateTextBoxName.Value, 2), _
Mid(MeDateTextBoxName.Value, 3, 2))
Case Select TestDate
Case #10/1/2001# To #9/30/2005#
' do nothing
Case Else
MsgBox "You have entered an invalid date."
Cancel = True
End Select
End Sub
 
Thanks this is great. I've copied what you had and changed the names to
fit. Now when I try to run it comes up with a "Compile Error/Syntax error"
highlighting "
Case Select TextDate"


Private Sub INDPTOS_Text_BeforeUpdate(Cancel As Integer)

Dim TextDate As Date
TextDate = DateSerial(Right(Me!INDPTOS.Value, 4), _
Left(Me!INDPTOS.Value, 2), _
Mid(Me!INDPTOS.Value, 3, 2))
Case Select TextDate
Case #10/1/2001# To #9/30/2005#
' do nothing
Case Else
MsgBox "You have entered an invalid date."
Cancel = True
End Select

End Sub

Is there something missing?


Donna
 
Hi Ken,

It still doesn't work. It still takes all dates from the data entry
screen. especailly those not in the range that is allowed in. The range
that should be entered is: 10/1/2002 through 9/30/2005. Nothing before
10/1/2002 and nothing after 9/30/2005.

Any ideas here.

Donna
 
Post what you now have as the code; let's see it all together. Also, what is
the control source for the textbox (what is its name?) into which the user
enters the date info? Show examples of what you're typing into the textbox
that should be accepted and shouldn't be accepted.
 
The text box name is INDPTDOS. This is field that the user is entering into.
Format is 99/99/0000. The table where is the date is stored is set up as
text, size 10. The date goes in as 99990000 without the "/".

Here is the code:

Private Sub INDPTDOS_Text_BeforeUpdate(Cancel As Integer)

Dim TextDate As Date
TextDate = DateSerial(Right(Me!INDPTDOS.Value, 4), _
Left(Me!INDPTDOS.Value, 2), _
Mid(Me!INDPTDOS.Value, 3, 2))
Select Case TextDate
Case #10/1/2002# To #9/30/2005#
' do nothing
Case Else
MsgBox "You have entered an invalid date."
Cancel = True
End Select

End Sub

What should go into the text box is keyed in by the user. Any date between
10/1/2002 through 9/30/2005. What is not accepted are dates before
10/1/2002 such as 9/30/2002 and no dates after 9/30/2005 such as 10/1/2005.


Donna
 
Do users enter
9/30/2005

or enter
09/30/2005

or enter
09/30/05

The code that I provided expects that 8 digits (not counting / characters)
are entered by the user.
 
Dates are entered as 09/30/2005.



Ken Snell said:
Do users enter
9/30/2005

or enter
09/30/2005

or enter
09/30/05

The code that I provided expects that 8 digits (not counting / characters)
are entered by the user.
Click to expand...
 
You say that the name of the textbox is INDPTDOS.

Your event procedure says that the name of the textbox is INDPTDOS_Text.

So, assuming that INDPTDOS is correct, and that INDPTDOS_Text is not
correct, your textbox is not running the validation code on its BeforeUpdate
event because the code is "in" a different procedure.

Open the form in design view, click on the INDPTDOS textbox, open the
Properties window, click on Event tab, click in "On Before Update" box,
click on three dots at far right, and open Visual Basic Editor.

Copy and paste the code steps from the existing BeforeUpdate event procedure
into this new event procedure. Then delete the current event procedure code
(INDPTDOS_Text_BeforeUpdate). Save the form.

Try it now.
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top