We help IT Professionals succeed at work.

Date Field problem

jodstrr2
jodstrr2 asked
on
Hi Experts,
I have a date question, I have a date field in a form called Terminated Date, I set it to a short date so that users just enter mm/dd/yy instead mm/dd/yyyy however, there is a little problem that it's ok that if user enter 12/31/29 then it shows 12/31/2029, but when user enter 12/31/30 then it will automatic change to 12/31/1930 instead 12/31/2030, can anyone tell me how should I fix?  I tested if I removed the short date format, it works fine, but if I add the short date format, it does that.  Please advice. (I'm using Ms Access 2010 front end and SQL 2005 back end)

thanks
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
In the Windows Control panel, there is a "Regional & Language Options" option.  Open this dialog, then go to the Formats tab, click the Customize this format button, and then select the Date tab.

At the bottom of that tab, you should find options for how to interpret two digit years.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
What you may need to consider is some program logic which checks the year value of that control, and if it is outside a certain range, prompts the use to select the appropriate year (or automatically changes it).
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
This is why it is always best to display the full year...
This works in all versions of Access and all versions of SQL server.

1929 is the cut-off date the MS decided on years ago, and there is no way to *easily* work around this.
Your really can't make you own cutoff date system, (Not worth the trouble IMHO)

If you notice, ...most programs (after after Y2K, (Year 2000)), will insist on the full 4 digit year.
Using the two digit year is "old-school" for lack of a better term.
;-)

JeffCoachman
CERTIFIED EXPERT
Top Expert 2010

Commented:
That is controlled by your Windows regional settings.  In my Windows 7:

Control Panel
Clock, Language & Region
Change date, time, or number format
On Region and Language dialog, Formats tab, click Additional Settings
On Customize Format tab, go to Date tab

Be very careful about messing with this, though, as it may have unintended consequences.

IMHO, you are better off requiring 4 digit year entry.
CERTIFIED EXPERT
Top Expert 2010

Commented:
Slow fingers :)
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Patrick,

Happens to me all the time.  I usually blame it on a coworker who walks into my cube and distracts me.

;-)

Author

Commented:
so I think the better way to fix it, is requiring the 4 digit year entry is correct?
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Probably, but if you know that all date values for this particular field should be in the future, you could simply write an afterupdate event that changes the year to post 2000

Private Sub txtDateField_AfterUpdate

if Len(me.txtDateField & "") = "" then exit sub

if Year(me.txtDateField) < 2000 Then
    me.txtDateField = Dateadd("yyyy", 100, me.txtDateField)
endif

End Sub

Author

Commented:
works perfect, thanks!!
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Note that when you asked:
"so I think the better way to fix it, is requiring the 4 digit year entry is correct?"

fyed replied:
"Probably, ..."

So he acknowledged that using the 4 digit year is still probably the best way to resolve the issue.

He also posted a valid workaround.
The kicker there is that it is now seems impossible for you to actually enter a date prior to 2000
So if you entered 1999, it would seem to be converted to 2099

So if you need to "back date" a date, it may be immediately converted to a date after 2000

As with all experts here, we rarely argue with another experts solution, especially if it answers the question as posted.

The bottom line is that if you used the 4 digit year, no external code or settings would be required.
...and you must make sure that all users know the rules...

;-)

Jeff
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Dale,

Did I get this right, or should the formula be something like:

If SomeDate<=1929 then
    Somedate=Dateadd("yyyy", 100, SomeDate)
end if

...or did I misunderstand something?
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Jeff,

In my final comment, I stated that if the OP knows that field must be in the future, they could use that simple code.  I used 2000 as my cutoff year, but they could use anything.  The issue in the OP was that dates greater than 12/31/29 will show up as 1999 if the user only types 2 digits for the year, so, you could change the syntax to:

if Year(me.txtDateField) < 2000 And (Year(me.txtDateField) Mod 100 > 29) Then

I obviously would not do this for a Date of Birth or Date of Death field, but might for something like an Expiration date for a new product or patent or something like that that has an extremely long life.

Dale
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
OK, then I misunderstood...

Sorry about that...
;-)
No offense was meant...

Jeff
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Jeff,

None taken.  We [experts] owe it to those asking the questions to voice alternatives and express the pro's and con's of our methodology.

I learn as much from the other experts in these discussions as the OP does.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.