Re: Date validation not working
From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 03/22/05
- Next message: Lauren B: "complex question"
- Previous message: Tony Williams: "Re: Help with set focus Error 2110"
- In reply to: melwester: "Re: Date validation not working"
- Next in thread: melwester: "Re: Date validation not working"
- Reply: melwester: "Re: Date validation not working"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 22 Mar 2005 09:52:59 -0500
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.
--
Ken Snell
<MS ACCESS MVP>
"melwester" <melwester@discussions.microsoft.com> wrote in message
news:7EB5BE01-A09D-4B84-A0DA-5E272E7312AF@microsoft.com...
> 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
>
> "Ken Snell [MVP]" wrote:
>
>> My typo -- change it to
>>
>> Select Case TextDate
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>> "melwester" <melwester@discussions.microsoft.com> wrote in message
>> news:119F2AE4-090C-4B45-B217-7F0D7110120C@microsoft.com...
>> > 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
>> >
>> > "Ken Snell [MVP]" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >> "melwester" <melwester@discussions.microsoft.com> wrote in message
>> >> news:02BD70C2-B4A3-43C2-A89A-FA5895CCB83B@microsoft.com...
>> >> > 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
>> >> >
>> >> > "Ken Snell [MVP]" wrote:
>> >> >
>> >> >> 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>
>> >> >>
>> >> >>
>> >> >>
>> >> >> "melwester" <melwester@discussions.microsoft.com> wrote in message
>> >> >> news:30A8408C-9BA2-4263-8911-2080E20C0EB0@microsoft.com...
>> >> >> > 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
>> >> >> >
>> >> >> > "Ken Snell [MVP]" wrote:
>> >> >> >
>> >> >> >> 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?
>> >> >> >>
>> >> >> >> --
>> >> >> >>
>> >> >> >> Ken Snell
>> >> >> >> <MS ACCESS MVP>
>> >> >> >>
>> >> >> >> "melwester" <melwester@discussions.microsoft.com> wrote in
>> >> >> >> message
>> >> >> >> news:D65DAC7B-F0CE-4421-8FE3-76B6E5D3479E@microsoft.com...
>> >> >> >> >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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
- Next message: Lauren B: "complex question"
- Previous message: Tony Williams: "Re: Help with set focus Error 2110"
- In reply to: melwester: "Re: Date validation not working"
- Next in thread: melwester: "Re: Date validation not working"
- Reply: melwester: "Re: Date validation not working"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
