Is there a setting to change the year of a date?

[snoopy80]

Happy Holiday
I have a column that accept data as mm/dd/yy format. the problem happens only at this time of year. When user enter 01/05 and press enter it will automatically set to 1/5/2005, but what it really should be is 1/5/2006. I assume it gets the year from the system but is there a way for me to set it so it show 2006 instead?
Thanks for help

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[fumei]

Why Skip? Or rather...why are they so much trouble, Skip? Because, as your FAQ points out....they are NUMBERS. People persist in thinking of them as objects, as things.

It is the conceptual difference between:

It is a date. - a thing
It is. - a numeric value.

It is gonna take a looooonnnnnnngggggggg time (expressed as Julian of course....) to work that one out.

Gerry

 

[SkipVought]

"...but is there a way for me to set it so it show 2006 instead?"

Yes!

Instead of entering
[tt]
1/5
[/tt]
enter
[tt]
1/5/2006
[/tt]


Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[DaleWatson123321]

Or even 1/5/6 is sufficient.

Regards, Dale Watson

 

[GDGarth]

I understand where you're coming from. A lot of database design has to do with anticipating errors, and avoiding them. It's easy to tell people to enter the year, but if they find out they don't have to, they may take shortcuts.

I created a text box called "TestDate", and put this code in the AfterUpdate event. The only problem is, this won't let you add a date more than 30 days less than the current date. If that works, you can use it and it will add one year to any date that fits the criteria.


Private Sub TestDate_AfterUpdate()
Dim DateVal As Date

If TestDate < Date - 30 Then
DateVal = DateAdd("yyyy", 1, TestDate)

TestDate = DateVal
End If
End Sub

Another alternative is to have the date entered as text, and convert it to a date after you test it. If someone doesn't enter the year, you can pop up a message box asking them to correct it before you save the data.