dates - 2 digit year

[RCDAwebmaster]

I'm running a query in classic ASP which fills a web calendar with events based upon the month and year. The data is stored in an access database of 2007 format.



I've noticed that if the year is 14, Access treats the year as 2014 and returns events rather than treat the year as 0014 and returning no events. How do I change this?

14 should be 0014 not 2014.

Mike

[June7]

Is the value stored in Access as a full date? Why using only 2-digit year in filter? Post the query SQL statement.

[RCDAwebmaster]

The website uses "events_month?m=5&y=2014" for May 2014.

If someone uses back arrow to go backwards or changes the url to "events_month?m=5&y=2014" they get May 2014

even if I try to change 14 to 0014, access still treats 0014 as 2014.

Yes events are stored in 4 year date. I think access does not like 0014 and returns 2014.

[June7]

Okay, this is new to me. Just did some testing. Access isn't handling date values prior to year 0100.

Tested in the VBA Immediate Window

?Format(#1/1/0100#,"mmm dd, yyyy")
Returns Jan 01, 0100

?Format(#1/1/0014#,"mmm dd, yyyy")
Returns Jan 01, 2014
same for
?Format(#1/1/14#,"mmm dd, yyyy")

Tested in a query:
Criteria of #1/1/0014# or #1/1/14# is automatically changed to #1/1/2014#.
Criteria of #1/1/0140# is automatically changed to #1/1/140#.

The only solution I can suggest is to not use actual date values.

[ItsMe]

...Access isn't handling date values prior to year 0100...

Some reference material to that fact
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[RCDAwebmaster]

I thought about converting the date to text, but since I'm searching for events by date, I can't do that. Since the calendar only holds events ranging from 6 months prior to today and on into the future, I decided to limit how far back the calendar could go. I settled on January 1, 2000 as the low end. There is no upper limit. This eliminates the problem for now.

Down the road, I'll look to migrate the database from Access to SQL Server.