Results 1 to 2 of 2

Thread: Date validation

  1. #1
    Join Date
    Nov 2005

    Unanswered: Date validation

    okay this works perfect for me making the date be in the future. Heres what else i need to do. If this text box is left blank, i need it to display "Enter Date" how can i fit that validation in here.

    f Me.myTDateTxtBoxName < CDate(Now()) Then
    MsgBox "Your date entry is invalid. You must supply a date in the future.",vbExclamation, "Invalid Date Entry"
    Cancel = True
    End If

    i tried if mytdatetxtboxname = "" then
    msgbox "enter date"
    cancel = true
    end if

    that code does not work. How can I make it so someone has to enter a date value or it will not let the proceed to another field?


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    the following should handle NULL, empty string, and anything not recognised as a date by the client regional settings.
    if isdate(nz(yourDateBoxName,"")) then
      if yourDateBoxName < now() then
        msgbox "Enter a future date"
        msgbox "Enter a future date in the format " & format$(34567,"Short Date")
    this works for datetimes less than Now() ...meaning the time-componant (if any) of the user entry is taken into consideration.

    ...note that CDate(Now()) returns a datetime.
    the "date-only" entity you are looking for is a standard Access datetime with the time portion of the datetime set to zero. Now() returns a Variant(Date) and CDate(Now()) returns a datetime but IMHO the conversion is not necessary.

    ...note the use of format$() to show the user the required date format in HIS regional settings if his input was not recognised as a date. you-the-coder cannot (should not!) control the user's choice of windows' regional settings.

    "future" as defined by the above code is any time from one second after the routine runs. if you instead want the user input to be any day after today, you need a little function

    Public Function TodayEnd() as date
      dim fixDate as Date
      fixDate = Now()
      TodayEnd = DateAdd("s", -1, DateSerial(year(fixDate), month(fixDate), day(fixDate)+1))
    End Function
    make it public in a module if you will use it from several forms. if not, it can be private in the concerned form.

    using fixDate is paranoid!!
    it covers the extremely unlikely case that the date changes somewhere between the call to year() and the call to day()

    TodayEnd() returns a datetime representing the last second of the day when fixDate was filled with Now()

    so now your user input test reads:

    if isdate(nz(yourDateBoxName,"")) then
      if yourDateBoxName < TodayEnd then
        msgbox "Enter a future date"
        msgbox "Enter a future date in the format " & format$(34567,"Short Date")


    i guess that should read
    if yourDateBoxName <= TodayEnd then

    and you could probably use an input mask to prevent the user entering a time-componant (tho i'm not a fanatic of input masks since they often mess things up in an international envirionment)
    Last edited by izyrider; 11-20-05 at 06:05.
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts