Access Error


When entering 28/08/2014 into a date field on a form, i receive the message

"Date must be between 2000 & 2010"

I traced the code that is supporting that field, can anyone see why i would get that message?

The code it refers

Private Sub aChaseDate_AfterUpdate ()
Select Case IsDate (aChaseDate)
Case False

msgbox "Date must be between  "  & Year(Date)-1 &  " and "  &  Year(Date) +30
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
It needs a rewrite.

First, IsDate checks for a valid date expression, but should always return True if your control has applied a date format for property Format.

Second, for the validation of the year entered, IsDate cannot be used.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Do you have any fields in your form's recordsource named "Date"?  If so, ideally rename them, since date is a reserved word/vba function.

If that is not possible, try either of the following:

msgbox "Date must be between  "  & Year(Date())-1 &  " and "  &  Year(Date()) +30  'Add parens to specify the date() function

Open in new window


msgbox "Date must be between  "  & Year(VBA.Date)-1 &  " and "  &  Year(VBA.Date) +30

Open in new window

Dale FyeCommented:
In addition to Gustav's and Miriam's recommendations.

The IsDate() function returns a boolean value if the value passed to it can be recognized as a date.  So in the CASE statement, if the expression (the value in your aChaseDate control) cannot be recognized as a valid date, your error message should indicate that it is not a valid date, not that the date must be between the current year (-1) and the current year (+30).  Additionally, since you can only test for one expression in a Case statement, I would recommend replacing that with an If statement, something like:

Private Sub aChaseDate_AfterUpdate ()

    If IsDate (aChaseDate) = False Then
         msgbox "Unable to recognize the entered value as a date
    ElseIf Year(cdate(aChaseDate)) < Year(Date()) -1 then
         msgbox "The year must be greater than or equal to " & year(date())-1
    Elseif Year(cdate(aChaseDate)) > Year(Date()) +30 then
         msgbox "The year must be less than or equal to " & year(date())+30
    End IF
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Your date is 28/08/2014.  Is your Windows date format set to dd/mm/yyyy?  Have you overridden that in the form control?
Seamus2626Author Commented:
Private Sub acActionDate_AfterUpdate()
On Error Resume Next
Call TrackForAudit(Forms!frmcorpmain!acMainID, Me.RecordSource, Screen.ActiveControl.Name, Me.Name, Screen.ActiveControl.OldValue, Screen.ActiveControl)
'the next line merely warns of a selected weekend 15 nov 99
If DatePart("w", Me!acActionDate) = 1 Or DatePart("w", Me!acActionDate) = 7 Then MsgBox Format(DatePart("w", Me!acActionDate), "dddd") & " " & Format(Me!acActionDate, " dd mmm yyyy") & " Selected", 64, "Weekend Selected"

Select Case IsDate(acActionDate)
Case False
    MsgBox "Date must be between " & Year(Date) - 1 & " and " & Year(Date) + 10
Case True
If Year(acActionDate) < Year(Date) - 1 Or Year(acActionDate) > Year(Date) + 10 Then
    MsgBox "Date must be between " & Year(Date) - 1 & " and " & Year(Date) + 10
End If

End Select

End Sub
Seamus2626Author Commented:
Thats the full sub guys, im helping a friend out, so have no deep understanding of this database
Seamus2626Author Commented:
It looks like the DB was built in 1999 and may have only been set to rub to 2010 - if i put in 28/08/2009, there would be no problem.....
Gustav BrockCIOCommented:
That doesn't have any influence.

The single reason for failure is that:


always return True.

So what you will have to do, is to compare the input date to a min and max value and - if not between these - call the messagebox.

Also, remove the "On Error Resume Next" until ready.

As someone already mentioned, you have a field named Date and that is interfering.  If
& Year(Date) - 1 & " and " & Year(Date) + 10 = 2000 to 2010 then Year(Date) MUST be something other than the current year which is 2014.  That would make the message:
Between 2013 and 2024.

Unless of course you have reset your computer clock to 2001.  But even then you would not get the range 2000-2010 with the posted code.
Luke ChungPresidentCommented:
If the text box for the date is bound to an actual date field, there's no need to have validation to check if it's a date. Access takes care of that without the need to code anything,

Unrelated to your error message, there may be field validation rules as a property of the form's control or the underlying table's field. Check that if you're still having issues with the date range.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.