• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

Date Field problem

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
0
jodstrr2
Asked:
jodstrr2
  • 6
  • 4
  • 2
  • +1
1 Solution
 
Dale FyeCommented:
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.
0
 
Dale FyeCommented:
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).
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
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.

 
Patrick MatthewsCommented:
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.
0
 
Patrick MatthewsCommented:
Slow fingers :)
0
 
Dale FyeCommented:
Patrick,

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

;-)
0
 
jodstrr2Author Commented:
so I think the better way to fix it, is requiring the 4 digit year entry is correct?
0
 
Dale FyeCommented:
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
0
 
jodstrr2Author Commented:
works perfect, thanks!!
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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?
0
 
Dale FyeCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK, then I misunderstood...

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

Jeff
0
 
Dale FyeCommented:
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.
0
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.

Join & Write a Comment

Featured Post

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.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now