Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Date Field problem

Posted on 2012-08-14
14
Medium Priority
?
610 Views
Last Modified: 2012-08-15
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
Comment
Question by:jodstrr2
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38293073
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38293080
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38293086
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38293099
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38293101
Slow fingers :)
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38293121
Patrick,

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

;-)
0
 

Author Comment

by:jodstrr2
ID: 38293138
so I think the better way to fix it, is requiring the 4 digit year entry is correct?
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 38293201
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
 

Author Comment

by:jodstrr2
ID: 38293249
works perfect, thanks!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38293499
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38293517
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38293603
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38294807
OK, then I misunderstood...

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

Jeff
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38295644
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

Featured Post

Independent Software Vendors: 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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 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