Dates such as 1/1/904 are not saved properly when saving query in Excel

[russi]

Please do not lecture me, as I am lecturing myself very well, thank you.
In the Access database some users put dates such as 2/2/904, instead of 2/2/2004.

Anyway, when I try to save the qeury showing such dates into Excel format (requested by ngt), it will not show.
How can I get it to show?

Thanks, in advance.

Russ

 

[jeremie_ingram]

Did you use an input mask for the field? What type of format is it in? If you left it as a text file, then you could run a query against it and create a code to seperate the 3 sections of the data by the /, then run a check against them, correct the wrong ones, then reassemble then into the proper format.

 

[The_Doc_Man]

In the Access database some users put dates such as 2/2/904, instead of 2/2/2004.

This cannot happen unless the field is a text field, not a date field. I have a check-register database that I use for a small home office situation. Every now and then I fat-finger the date field and Access won't even let me tab out of the field until I correct it. That's 'cause the field is a DATE data type, not text.

Don't need an input mask or validation code, don't need nothin' - and 904 as a year would be just bizarro enough that it probably would get rejected. 'cause it is NOT a 2-year date. (2/2/04 would work fine!) But 904 is NOT a 2-year date, so it must be a full date, and 904 is earlier than 1900, the system reference date, so it won't fly as a valid system date.

 

[Pat Hartman]

Unfortunately 2/2/904 is a valid date so no standard date edit will reject it unless the edit includes a range check. If your field is defined as a date, you can add a validation rule to make sure that dates fall into a reasonable range.

>=#1/1/1900# And <=#12/31/2050#

If your date is text, the problem requires code in the form's before update event.

In either case, you need to fix the existing bad data.

Doc, Access uses a double precision number to represent dates. Any date earlier than 12/30/1899 is represented as a negative number. I don't remember what the boundaries are but 904 falls within them. I think the earliest year supported is 100 and the highest is 9999.