Validate Form Date only for Current Year (1 Viewer)

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
I wrote this validation rule which I will add on all my forms for dates requiring dates meant for only current year. I tested it and only takes dates for 2016 which is correct. I want to confirm from more seasoned developers that this will work year to year so in 2017 will only take 2017 dates. Note: I realize I code also hard code it but want to make it easier to change. Thoughts?

>=DateSerial(Year(Date()),1,1) And <=DateSerial(Year(Date()),12,31) Or Is Null
 

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
Also any way I can allow dates to be restricted for past two years? So in 2017, only dates from 1/1/2015 to 12/31/2016 can be entered and in 2018 only 1/1/2016 - 12/31/2017 can be entered
 

KenHigg

Registered User
Local time
Today, 06:07
Joined
Jun 9, 2004
Messages
13,327
I would move this to the form and cancel the update event if the tests fail. I like putting all of this type of validation in the presentation layer so the data layer is more portable. Also narrows down areas to poke around in when trying to figure out how it works down the road :)

Hope that makes sense...
 

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
It doesnt make sense to me but Im guessing your talking about puttting into VBA.
 

KenHigg

Registered User
Local time
Today, 06:07
Joined
Jun 9, 2004
Messages
13,327
In short the logic is starting to get a bit more complex than the table/field validation feature can manage. So yes, create a vba routine that checks the date before it allows updates. Have you done vba before?
 

KenHigg

Registered User
Local time
Today, 06:07
Joined
Jun 9, 2004
Messages
13,327
What version of Access are you using? I'll cobble something together...

Edit: See if you can open this -
 

Attachments

  • Database2.accdb
    508 KB · Views: 100
Last edited:

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
Thanks. I do use VBA frequently and it is part of my job. I just havent coded this type >=DateSerial(Year(Date()),1,1) And <=DateSerial(Year(Date()),12,31) Or Is Null. Thanks so much. Ill take a look.
 

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
I just looked at it. Im trying to make it more portable from year to year so it doesnt have to be changed from year to year. So in 2017 they would have to change it to 2017. Some cases the dates are for the last two years, some for only current year, and some for last (5) years which is how I came up with that rule which works.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:07
Joined
Jan 23, 2006
Messages
15,361
You need to get all the facts that make something:
-current year only
-last 2 years
-last 5 years

What determines how long something should be kept/allowed?

Date() returns the current Date whether 1923 or 2018
so using Date in your validation routine doesn't require manual intervention to adjust to 2017.

But Year(Date) will get the current calendar year --why do you need the month an day?
I think a little more analysis of what exactly you are dealing with is needed.

Good luck.
 

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
Interestingly the form before update didn't work but I used it on an Event Exit and it worked fine. Thanks.
 

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
Too many dates on form to roll it into form update. It would have to be on exit for the field. Date is always entered 00/00/00 and various validation rules. Thanks
 

KenHigg

Registered User
Local time
Today, 06:07
Joined
Jun 9, 2004
Messages
13,327
Cool thing is now that you have in it in a module you can create a table for configuration data like this and if you need to change the date parameters all you have to do is change the value in the table and not have to poke around in code. You go from a hard coded parameter to a dynamic one. This is a powerful concept. It's kinda what Windows registry is all about...
 

JMarcus

Registered User.
Local time
Today, 03:07
Joined
Mar 30, 2016
Messages
89
Hey Guys,

When I use this code which works in a test database it wont work in the box in my database. It comes up with Run Time Error "94" Invalid use of Null. Date entered has to be in the current calander year. Code is as follows:

Private Sub dataentry1_BeforeUpdate(Cancel As Integer)
If Me.dataentry1 >= DateSerial(Year(date), 1, 1) And Me.dataentry1 <= DateSerial(Year(date), 12, 31) Or IsNull(Me.dataentry1) Then


MsgBox "Incorrect Date"
Cancel = True
End If

End Sub
 

Users who are viewing this thread

Top Bottom