INvalid dates February

[Jen0dorf]

Hi

just thought I'd share this, in case anyone else had hair tearing moments.



Spent all morning trying to import a spreadsheet with a column named date format dd-mm

I kept getting convert errors but could see nothing wrong.

The the proverbial penny dropped I had 12 records with the "date" 29-02

Made a note of the line no, changed it to 28-02 and it imported perfectly. I then manually changed back to 29-02.

Can't explain it- not clever enough but it seems Access has a thing about leap years

cheers

Ian

[ranman256]

Import the 'date' to 2 fields ,a string field, AND a date.
The invalid dates will not post, but you can see the actual text in the string version.

[Jen0dorf]

HI

thanks but why is 29/2 an invalid date?

Ian

[ssanfu]

Spent all morning trying to import a spreadsheet with a column named date format dd-mm

You really have a field named "Date"? You do know that "Date" is a reserved word in Access and shouldn't be used as an object name?
Plus, it is not very descriptive. "Date" of what?? Birth, Death, Internment,......

format dd-mm

What does the underlying data look like if you remove the formatting? The access gnomes try their best, but sometimes gets confused.
A date formatted like "dd-mm" is a string. But you want Access to shove it into a date field.

(IMO) It would be better to remove the Excel formatting before trying to import. Access requires American format..... for better or worse.

[vicsaccess]

just my curiousity, post #3. would that be because there is no year attached? this date is associated with leap year. so a standard year would not have febuary 29th?

[Jen0dorf]

HI

thats true the church records list date in one column and year in the other!!

The society have permission to record the records electronically but apparently have to retain the church format.

Your idea could be the reason

cheers

Ian