import spec: errror when importing date

My date is in this format "YYYYMMDD" and when i import it i get the error: "Type Conversion Failure"
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if it's a char field in your source, then in your Access database, try to make it as a Char field as well. After the importing, you can do the necessary updates to convert the field from char to a proper date.

OR you can format the date in the source as a proper formatted date field, so we can try to avoid any conversion errors.
vbnetcoderAuthor Commented:
But this is a valid date format right? I don't think i should have to make it into a char field
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>But this is a valid date format right?
"YYYYMMDD" is not a valid date value (that's why you hitting conversion error, I assume your target table's field is a date field?), but "YYYY-MM-DD", "DD/MM/YYYY" or "DD MMM YYYY", etc is.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Rey Obrero (Capricorn1)Commented:
change the Data Type of the destination field to Text in your import Specification

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
for my purposed i decided it would work fine to make date fields text
PatHartmanCommented:
I see that you have marked the question answered but you never got an accurate answer.  Yes, you can get Access to import this field as a date.  You just need to know how.

When you are using the import wizard, you can define this date format and Access will import it as a proper date field.  At any point in the wizard, click on the advanced button.  You will see options to choose a data field order.  Choose YMD.  You also have options that specify delimiters or no delimiters and leading zeros or not.  Choose no delimiters and leading zeros.  Then specify that column as a date.  Save the import spec and name it before clicking the finish button.  That way you can use the import spec in the TransferText method to automate the import.
Rey Obrero (Capricorn1)Commented:
<When you are using the import wizard, you can define this date format and Access will import it as a proper date field.>

NOT with data written  "YYYYMMDD"
PatHartmanCommented:
Yes, you can.  I do it with files I get from a vendor.  The date actually comes to me as mmddyyyy but it doesn't matter since you can specify the field order.  

I included a sample .csv file in the import.  Use the import spec I created.
ImportFlatDate.zip
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.