Max Date Allowed by Access

[Commish]

What is the maximum permitted or allowable value for a date in MS
Access 2003?

I am converting some spreadsheet data into access and am using the
DateSerial function to convert 8 character number strings like
"19911001" into a StartDate. This works perfectly.

However when I convert the string "29991231" into an EndDate, I get a
"Data Type Mismatch in Criteria Expression" error. I am wondering if I
have bumped up against the maximum permitted date in Access.

 

[Dirk Goldgar]

What is the maximum permitted or allowable value for a date in MS
Access 2003?

I am converting some spreadsheet data into access and am using the
DateSerial function to convert 8 character number strings like
"19911001" into a StartDate. This works perfectly.

However when I convert the string "29991231" into an EndDate, I get a
"Data Type Mismatch in Criteria Expression" error. I am wondering if I
have bumped up against the maximum permitted date in Access.

No, that's not it. From the help entry on the Date Data Type:
<QUOTE>
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
that represent dates ranging from 1 January 100 to 31 December 9999 and
times from 0:00:00 to 23:59:59.
</QUOTE>

My guess is that it's something wrong in your code, but you'd have to post
the code for us to diagnose the problem.

 

[Commish]

No, that's not it.  From the help entry on the Date Data Type:
<QUOTE>
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
that represent dates ranging from 1 January 100 to 31 December 9999 and
times from 0:00:00 to 23:59:59.
</QUOTE>

My guess is that it's something wrong in your code, but you'd have to post
the code for us to diagnose the problem.

Dirk,

Here's the code that works and converts the data correctly:
StartDate: DateSerial(Left([DeCA Org]![StartDate],4),Mid([DeCA Org]!
[StartDate],5,2),Right([DeCA Org]![StartDate],2))

And here's the code that doesn't even convert and generates a datatype
mismatch error:
EndDate: DateSerial(Left([DeCA Org]![EndDate],4),Mid([DeCA Org]!
[EndDate],5,2),Right([DeCA Org]![EndDate],2))

UPDATE: I solved my own problem. Out of 1700+ rows, there's 1 blank
cell in the middle of this column. Of course, that function will error
out of the length of the string is zero.

I've double checked the data that is being converted is datatyped as a
number. So, I'm confused as to why one would work, and another column
if data won't work. Which is why I was thinking that maybe it was a
date range issue. I learned that the max year in Access is the year
9999 - I made a table, made a date column and plugged in values until
I found one that wouldn't go into Access. Year 9999 will be accepted,
Year 10000 will not.