Date is out of range or is in an invalid format. (Error 3175)

Question

1.00/5 (2 votes)

See more:

I'm importing a excel data into MS access Sharepoint linked tables and i get the Date is out of range or is in an invalid format for a 12/31/9999 date.How can i fix this issue? please help me

What I have tried:

This is my importing code
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9,"TAMIT_NA_EMEA_Extract", strItem, True

Posted 12-May-17 0:32am

Siva Krishna

Updated 15-Jan-20 3:26am

Add a Solution

Comments

Richard MacCutchan 12-May-17 6:41am

You fix the issue by finding out why that date is not acceptable.

Siva Krishna 12-May-17 6:47am

Only These dates(30/12/9999,30/12/9998) are not accepting.

Richard MacCutchan 12-May-17 7:12am

Sorry, but you have given us no context so we have no idea where or why this may be happening.

CHill60 12-May-17 6:42am

Does it work for dates that are not quite as far in the future?

2 solutions

Solution 2

I had dates entered as 00:00:00 once removed it loaded OK
I just sorted the date fields oldest to newest to find them as entering 00:00:00 as criteria didn't always catch them.

Posted 15-Jan-20 3:27am

Member 14717215

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Accepted Answer · 2017-05-12T02:57:00

Solution 1

It seems to be a limitation of SharePoint. A web research shows that entering wrong dates into a SharePoint form issues the error message

Quote:
You must specify a valid date within the range of 1/1/1900 and 12/31/8900

So you have to check the Excel date and limit it to the above range or ensure that the Excel sheet does not contain such dates.

Posted 12-May-17 2:57am

Jochen Arndt

Comments

ZurdoDev 12-May-17 9:03am

+5 for knowing how to google. ;)

Jochen Arndt 12-May-17 9:07am

Thank you.

But my google-foo was not strong enough to find a MS reference about this limitation beside some forum posts.