[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Error

Posted on 2014-08-29
10
Medium Priority
?
266 Views
Last Modified: 2014-09-04
Hi,

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
me.undo
0
Comment
Question by:Seamus2626
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 400 total points
ID: 40292243
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.

/gustav
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 40292248
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


or

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

Open in new window

0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 40292321
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
         me.aChaseDate.undo
    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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 400 total points
ID: 40292624
Your date is 28/08/2014.  Is your Windows date format set to dd/mm/yyyy?  Have you overridden that in the form control?
0
 

Author Comment

by:Seamus2626
ID: 40292732
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
    Me.Undo
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
    Me.Undo
End If

End Select

End Sub
0
 

Author Comment

by:Seamus2626
ID: 40292736
Thats the full sub guys, im helping a friend out, so have no deep understanding of this database
0
 

Author Comment

by:Seamus2626
ID: 40292738
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.....
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40292763
That doesn't have any influence.

The single reason for failure is that:

    IsDate(acActionDate)

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.

/gustav
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40292856
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.
0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 400 total points
ID: 40302692
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question